*In this article, you will understand adding numbers with a criterion by following six examples from simple to complex. For resolving our queries, we will use Excel SUMIF and SUMPRODUCT functions.*

Excel categorize SUMIF and SUMPRODUCT function under Math functions. This function adds all numbers in a range of cells that meet specific criteria.

Let’s first refresh our understanding of the SUMIF function.

In this post, you will go through **Six** distinct examples for a stronger understanding of adding value in different scenario.

The **key example** which will you cover here are:

#### Table of Contents

### Download Your Example Excel File

The SUMIF formula in Excel takes three arguments out of which one is optional. The function follows a format where a user needs to enter *Range* and *Criteria* while *sum_range* is optional.

Range: *A lookup range to apply criteria.*

Criteria: *The criteria that must satisfy in the given Range.*

Sum_ range [Optional]: *The range of cells to add if it satisfies the condition.*

**Remember:** Excel starts calculating innermost parenthesis than work outward. You can also debug the formula using a shortcut** F9** function key or under the formulas tab click on Evaluate formula.

**Sum One Column based on Another**

Adding numbers referencing to the next column where some cells are empty and remaining fills with text, number, dates, etc. In our case we have numbers.

QUERY:

In this example, we have the columns Receipt number, Sales and Payment left. Our task is to add all Sales *(column C)* if value in the range *(D4 to D13)* and ignore if empty.

Formula to implement:

**=SUMIF(D4:D13,”<>”,C4:C13)**

Result **=43351**

Here, we have selected range from *D4 to D13*, where criteria are “<>” (not equal) and then sum range is C4 to C13 if TRUE, therefore it adds all given range which is not empty.

If you want to reverse the condition and want to add values based on the empty cells in next column the criteria parameter will change from “<>“ to “” (empty). The new formula will sum all sales numbers in a *column (C)* with reference to the* column (D)* with no number or ignoring any text in column (D) range *E4 to E13*. Check the below formula,

**=SUMIF(D4:D13,””,C4:C13)**

Return: **41110 ‘***Added cells only if next cell if empty.*

## Cumulative Addition to Matching Criteria** **

When you want to calculate the a column’s SUM that is filled with numbers if some cells contain no numbers (text) then display text. We can implement function to check error along with SUMIF formula.

QUERY:

In this example, we have columns Item code, and Units. Our task is to sum similar code units’ (Cumulative) values if there is text and no numbers then display text “BULK”.

*Formula to implement:*

It will return cumulative numbers (successive additions) of **same Item code** other than will show the text in result if ‘Bulk’. Like here in Cell G3 it displays text “BULK” and Cell G4 it displays 15 as there is only one code, whereas in cell G5 & G10 it displays 43 (23+20) as there are two similar codes (TU294).

**Sum and Count only IF Similar Text**

When you want to know the average of a column with similar text in multiple rows. Here we will implement COUNTIF formula along with SUMIF to know the average.

QUERY: ** **

In this example, we have columns Product and Quantity *(option 1, option 2, option 3)*. Our task is to add all numbers of the product *(Watch)* within option 1 category and then divide it by counting how many times Watch occur (3), to know the average quantity of mobile in the ‘option 1’ category.

*Formula to implement:*

Result: **9.67**

(14+11+4)= 29, count =3,

(29/3)= 9.67

It first adds the number based on criteria “Watch” in the given range *C4 to C13* then it divides after counting on the basis of the condition (Watch) in the given range. As mobile comes three times in range *C4 to C13* so it divides by 3 after adding all watch in option 1 of quantity.

Decrease the decimal places under the Excel **HOME** tab.

**Sum Specific Column with similar Text**

Adding respective column value with similar text in multiple rows in Excel. We will use INDEX and MATCH along with SUMIF formula.

QUERY: ** **

In this example, we have columns Product and Quantity of option 1, option 2, option 3. Our task is to know the lookup value of Mobile in Option 3 range, and then returns addition of specific *(TRUE) *cell given by “Mobile” of column (C) product.

*Formula to implement:*

**Result**: 46

*(18+12+16)= 46* ‘Only If criteria is TRUE

MATCH function first returns the position of column “option 3” in the given range *D3 to F3* i.e. ‘**3′ **and INDEX returns all value of Option 3. Both Excel function together make the selection of Options dynamic.

**Note**: Creat a Drop-down list for easy working with our formula in such scenario.

**Sumproduct with Different Number of Rows**

Adding column if similar text then returns the sum of the product. For this, we will apply SUMPRODUCT along with the SUMIF formula.

**QUERY**: We have price and invoice quantity. Our task is to add quantity of given product and then multiply them with price in another table, returning the total invoice value.

Formula to implement:

**=SUMPRODUCT(SUMIF(B7:B10,B3:B4,C7:C10),C3:C4)**

**Result**: 430

(17+3)= 20, (2+1)= 3 ‘Add Pen and Pencil Quantity

(20*20)= 400, (10*3)= 30 ‘Multiply Price and Quantity

(400+30)= 430

Here first we add all the numbers on the basis of given criteria i.e. “pen and pencil” in the given *sum_ range* B7 to B10 and then multiply the added number with the price *(column C)* and gives sum of product.

**Sumproduct with Similar Heading**

When you want to add multiple values in columns and rows if it is with the same name, apply below formula. We used here SUMPRODUCT formula.

*QUERY:*

We have columns Name, line 1, line 2, line 3, line 4, line 1, line 3, line 6, and line 7. Our task is to sum all the numbers with similar Name (Line 1) and returns the sum of the product. Here we have duplicate rows and columns name, so we have 2 criteria, one for rows and one for columns. In such case we will be using Excel’s SUMPRODUCT function.

**Formula to implement:**

**=SUMPRODUCT(($F$3:$F$11=G$15)*($G$2:$N$2=H$14),$G$3:$N$11)**

* Result:* 282

It returns TRUE if “Joe” or “Line 1” in the given range otherwise return FALSE.

(63+50+69+13+74+13)= 282 ‘Add only TRUE with Joe

First, it checks from the given range *F3 to F11* where criteria name (G15) “Joe” so it returns TRUE otherwise FALSE if the name is not Joe. Then it check the second criteria “line 1” it also returns TRUE that meets criteria in the range G2:N2 otherwise it returns FALSE. After that, it add all numbers in the range G3 to N11 which meets both the criteria **Joe** and **line 1** comparing with columns and rows.

You can also give a name to each sheet or range for better understanding and easy to recognize by clicking formulas tab and then click define name range for naming the set of cells.

**Note:** **“**&” operator in Excel is a type of **concatenate **which is used to join two strings, values or results.