How to use SUMIF function in Excel

SUMIF is a popular function in Microsoft Excel that returns the summation of a range of cells that meet a single criteria or condition.

Arguments of the SUMIF function syntax

• range- (required): The range of the criteria to be evaluated.
• criteria- (required): The criterion or the logic behind the summation.
• sum_range- (Optional): The range of cells to sum up.

A better understanding of the arguments

• Blank and text values are ignored in a range of cells.
• The condition can be applied to excel format dates, numbers, and text.
• Text strings in criteria must be enclosed in double quotation (“”).
• The SUMIF function only supports the logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.
• sum_range should be the same size and shape as in the range specified.

Read More: How to round up number to 2 decimal places in Excel

Identify a SUMIF problem

I have prepared an example worksheet to implement the SUMIF function. Please see the screenshot below.

It is a sales summary of five states in the USA. We need to find out the total sales quantity in five different states using the SUMIF  function.

How to use SUMIF function in Excel

Criteria:  Text value

Consider the total sales quantity for the state AL, If we remember the syntax of the SUMIF function, it is SUMIF(range, criteria, [sum_range]).

Here,

• the range is C4:C10 where criteria are to be evaluated.
• the criteria is “AL” or cell C15.
• the sum_range is D3:D9

So,

• The formula for AL state is =SUMIF( C4:C10,”AL”,D3:D9)
• The formula for AK state is =SUMIF( C4:C10,”AK”,D3:D9)
• The formula for AZ state is =SUMIF( C4:C10,”AZ”,D3:D9)
• The formula for NJ state is =SUMIF( C4:C10,”NJ”,D3:D9)
• The formula for OH state is =SUMIF( C4:C10,”OH”,D3:D9)

Alternatively, We can try,

• The formula for AL state is =SUMIF( C4:C10,C15,D3:D9)
• The formula for AK state is =SUMIF( C4:C10,C16,D3:D9)
• The formula for AZ state is =SUMIF( C4:C10,C16,D3:D9)
• The formula for NJ state is =SUMIF( C4:C10,C17,D3:D9)
• The formula for OH state is =SUMIF( C4:C10,C18,D3:D9)

Criteria: Date value

Here is another sale summary where every month’s sale quantity is mentioned. We want to find out the number of sales before and after June 2022.

If you have multiple criteria, the SUMIF function will not work. For multiple criteria, there is another function in excel called SUMIFS. I hope, this article on how to use SUMIF function in Excel is helpful to you.