The SUM function in excel allows you to add values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

Syntax

SUM(number1, number2, ...)

Arguments

The SUM function lets you add values in cell ranges, arrays, constants. You can have up to 255 different arguments.

Table of Contents

1. Add values in a cell range
2. Add values in an array
3. Multiple cell ranges
4. Numbers and text
5. Boolean values
6. Expanding cell reference
7. SUM with a condition
8. SUM with multiple conditions
9. SUM - VBA example
10. Download excel *.xlsm file
11. SUM formula examples

Add values in a cell range

The SUM function lets you add values in a cell range, like this = SUM(B3:B7), instead of adding values in a formula using the plus sign, like this =B3+B4+B5+B6+B7.

Table of Contents

1. Overview
2. Arguments
3. Approximate match
4. Horizontal and vertical lookup
5. Horizontal and vertical lookup - INDEX + MATCH
6. HLOOKUP - multiple tables
7. VBA Example
8. Download excel *.xlsm file
9. Functions in this post

Overview

The HLOOKUP function lets you search the top row in a data range for a value and return another value on the same column in a row you specify.

HLOOKUP stands for horizontal lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look horizontally in the top row of the data array. VLOOKUP stands for vertical lookup but that is another post.

Syntax

Quick example

The HLOOKUP function in cell E2 searches the top row of cell range B5:E14 for value "Item" and it is found in the second column. The third argument tells the function which row to fetch the value from, in this case it is four.

The intersection of the second column and the fourth row is value DD and that is the returning value in cell E2.

Formula in cell E2:

Arguments

Table of Contents

1. Overview
2. Arguments
3. Approximate match
4. Related tables
5. Dynamic arguments
6. INDEX + MATCH
7. VLOOKUP error
8. VBA Example
9. Using multiple conditions in VLOOKUP
10. Download excel *.xlsm file

Overview

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.

VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.

Formula in cell C3:

The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.

Minh Hung asks:

Answer:

By examining your formula it seems that you have different levels with search words. The word found with the lowest level should be returned, leave the remaining.

Example 1, cell A2 contains "test branch wage". "branch" is found on level 1 (cell E3) and "wage" on level 4 (cell F6). However "branch" is on the lowest level of the two so Precol. (cell H3) is returned in cell B2.

Sankalp asks:

Answer:

The worksheet above shows you km in column A and highlighted cells in column B, if they are complete in the excel defined table.