How to Use the SUMIF Function in Excel

The SUMIF Function in Excel – Quick View

Introduction to the Excel SUMIF Function:

Summary:

This function adds the cells specified by a given condition or criteria.

Syntax:

=SUMIF (range, criteria, [sum_range])

Arguments:

Argument	Required/Optional	Explanation
range	Required	The range of cells to evaluate by criteria.
criteria	Required	The criteria: an expression, a number, a text, a function, or a cell reference that defines which cells to add.
sum range	Optional	The cells to add to combine cells that are not defined in the range argument.

Note:

In the criteria, wildcard characters can be included: a question mark (?) to match a single character, an asterisk (*) to match a sequence of characters. Like 6?”, “apple*”, “*~?”
sum_range should be the same size and shape as the range.
The SUMIF function can only have a single condition.

Example 1 – Calculating a Sum with Numeric Criteria Using the SUMIF Function

To count the total sales whose price was more than $1000 in H7.

Enter the formula in H7.

=SUMIF(E5:E16,">1000")

E5:E16 refers to the Sales column.

Formula Breakdown:

E5:E16 is the range for the sum operation.
“>1000” is the criteria. If the sales value is more than $1000, it will be counted. Otherwise, it will be ignored.

Press ENTER.

The output is $26,700.

Example 2 – Finding a Sum with Text Criteria Using the SUMIF Function

Calculate the sales in the Fruits Category.

Enter the formula in H8.

=SUMIF(C5:C16,"Fruits",E5:E16)

Formula Breakdown:

C5:C16 is the range to check the criteria.
“Fruits” is the condition or criteria. It checks if the Category is Fruits.
E5:E16 is the sum range.

Press ENTER.

The output is $14,700.

Example 3 – Use the SUMIF Function with Wildcard Characters for a Partial Match

To calculate the total sales of Apples.

Enter the formula in H8.

=SUMIF(B5:B16,"*Apples",E5:E16)

Formula Breakdown:

“*Apples” will find the name Apples or a name in which the first or last part is apples.

Press ENTER to see the output.

Example 4 – Calculating the Sum with Date Criteria

To get the sales of foods sold after 04/01/2021.

Enter the formula in H8.

=SUMIF(D5:D16,">"&DATE(2021,4,1),E5:E16)

Formula Breakdown:

“>”&DATE(2021,4,1) is the criteria. “>” is used to find the greater dates. The ampersand (&) is used to concatenate the formula and text. The DATE function is used to give the date input.
The DATE function accepts three arguments: year, month, and day. (If you want to know more about this function you can check this Link)

Press ENTER.

This is the output.

Read More: Excel SUMIF with Date Range

Example 5 – Calculating the Sum with the OR Criteria in the SUMIF Formula

To calculate the total sales of Vegetables, or sales greater than $1000.

Enter the formula in H8.

=SUMIF(C5:C16,"Vegetables",E5:E16)+SUMIF(E5:E16,">1000",E5:E16)

Formula Breakdown:

SUMIF(C5:C16, “Vegetables”, E5:E16) finds the rows in the Category: Vegetables.
The plus sign (+) is used for the OR
SUMIF(E5:E16,”>1000″, E5:E16) finds the rows where Sales are greater than $1000.

Press ENTER.

This is the output.

Example 6 – Applying the SUMIF with a Criteria Array

To count the total sales in the Category Fruits and Dairy:

Enter the formula in H8.

=SUM(SUMIF(C5:C16,{"Fruits","Dairy"},E5:E16))

Press ENTER.

This is the output.

Example 7 – Using the SUMIF with Date Range (Month and Year) Criteria

Find the Total Bill.

Enter the formula in C13.

=SUMIF(D5:D10,"="&C12,G5:G10)

Press ENTER.

This is the output.

Read More: [Fixed] Excel SUMIF Not Working

SUMIF Vs SUMIFS in Excel: What’s the Difference?

Both the SUMIF and SUMIFS function add the values of all cells in a range that meet a given criterion, but:

The SUMIF function adds all cells in the range that match particular criteria.
The SUMIFS function counts how many cells in a range meet a set of criteria.

To find Sales of Apples in Branch 1: