How to SUMIF in Excel?

SUMIF is one of the useful function in excel. This function returns the total sum of the specified range when the specified criteria is met by the specified range. SUMIF function ease our work if we are in search of the sum, based on the criteria.

The syntax of the SUMIF function is as follow:

= SUMIF(range, criteria,[sum_range])

The range is the range of cells which has to be evaluated against the specified criteria.

The criteria is the condition which is evaluated against the range specified. Criteria can be a number, text, function or a cell reference.

The sum_range is the range of cells whose values are to be actually added.

Note: SUMIF is not case sensitive. If the ‘sum_range’ is not specified in the formula, it returns sum of values in ‘range’.

In this section, we will explore the working of SUMIF functions. So let us start.

Steps to SUMIF in Excel 2016:

Basic Use

The basic task of the SUMIF function is to return the sum of the values in the range specified by us if they meet the criteria specified by us.

Let us see an example below:

Here, the SUMIF function returns the sum of all the values present in the range C3:C15 and are “>200”.

Utilizing the SUMIF function fully, the example below shows the sum of the number of days where sales are greater than 200.

SUMIF with Criteria in Another Cell

While using SUMIF if you want to include the value present in another cell as, criteria you can do it with the help of concatenation. Like in the example below, while using SUMIF I have concatenated “>” with the cell E9 using ‘&’. SUMIF function here, return the sum of the number of days where sales are greater than the value present in E9.

SUMIF with Date Criteria

You can also include a date in the criteria section of the SUMIF function. The example below shows the sum of all the number of sales which are done after the date 16-02-2020.

The example below shows the sum of the number of sales done today.

SUMIF with Not Equal to Criteria

To mention, ‘not equal’ to in the criteria section you have to use ‘<>’ operator. The example below shows the sum of the number of days where the number of sales is not equal to 100.

SUMIF with Wildcard

SUMIF function allows you to use wildcards in the criteria section. Like the below example shows the sum of the number of sales done by the staff whose name has exactly four characters.

The example below shows the sum of the number of sales done by the staff whose names are starting with the alphabet ‘J’.

The example below shows the sum of the number of sales done by the staff whose names are ending with ‘a’.

So, this is all about the SUMIF function in excel. We have explored the function in various method but it’s not all, you can explore it to find more. Hope the content is useful. Keep reading.