The SUMIFS function is probably one of the most useful functions with Excel that you will ever use. Rather than summing data by a fixed range, the SUMIFS function will allow you to sum values from a column or row based on the criteria in a corresponding column or row.

Before we dig into this, I want to preface that the SUMIF function within Excel is redundant. The SUMIF function allows you to SUM data based on only one criteria argument. This means that if you wish to add more arguments you will have to re-order your statement.

Syntax

Single criteria SUMIF

• =SUMIF(criteria_range1,criteria1,sum_range)

Multiple criteria SUMIFS

• =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

The general formula would appear as below, in an example which is asking to sum all of the values in cell range C2:C10 based on the criteria range B2:B10 that matches the criteria within cell D2.

• =SUMIFS(C2:C10,B2:B10,D2)

Explanation

In this example we want to sum the total amount of black vehicles crossing a toll bridge during July and August 21. The first argument in the formula bar is the range of values you wish to sum, followed by the criteria range for the colour of the vehicles. The last argument is the criteria parameter which is denoted by cell reference G3 but could be replaced by the text reference “Black” as an alternative.

SUMIFS with multiple criteria

• =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)

To use a SUMIFS statement against multiple criteria the standard formula is used and followed with an additional criteria range and criteria parameter. In the example below the first criteria argument looks for the vehicle colour in column C and the vehicle type in column B.

SUMIFS with date ranges

• =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,”>=“&date)

Excel can use date ranges as an argument for criteria such as the above statement which asks the second criteria range in the statement to be greater than or equal to the date in a given cell reference.

This is illustrated in the image below for cars on or after Aug-21.

SUMIFS excluding on a criteria

• =SUMIFS(sum_range,criteria_range1,”<>“&criteria1)

In some instances rather than setting a criteria on returning values for a specific parameter it is possible to exclude certain parameters. For example, if we wanted to exclude SUVs from our SUMIFS formula the result would look like the image below.

Limitations

There are some obvious limitations to SUMIFS formulas, the main one being the inability to sum two different criteria parameters on the same criteria range. This is due to the formula being unable to satisfy both arguments. One long-winded way would be to copy the entire SUMIFS statement and add it to the end of the first statement with a ‘+’ to sum the two together.

A much more efficient way around this problem is incorporating the SUMPRODUCT function!