Kornilios Ampatzis/ April 18, 2019/ Functions

The SUMIFS function adds the cells that meet all its multiple criteria. Even though its logical elements, it is part of the Mathematical functions of Excel.

We have prepared a tutorial on the SUMIFSfunction to explain it in a bit more detail.

The syntax of the function is the following:

SUMIFS(Sum_range, Criteria_range1, Criteria1, [Criteria_range2, Criteria2, ...])

Sum_range: The cell range we want to sum.
Criteria_range1, Criteria_range2, … : Criteria_range1 is required. The rest (up to 127) are optional. They are the range of cells that have to meet certain criteria.
Criteria1, Criteria2, … : Same as above only the first is required. The rest (up to 127) are optional. You cannot enter Criteria without a Criteria_range and vice versa. They contain the criterion (expression, number, reference…) that the respective criterion_range has to meet.

If a cell in a criteria range is empty, SUMIFS considers it a zero.

TRUE is considered 1 and FALSE 0.

Each Criteria_range must be the same size and shape as Sum_range.

You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Click on the button to practice using this function, with the help of our Online Assessment Tool:

Practice

Here are two examples of how to use the SUMIFS function:

In the cell H4 calculate the sum of all orders with amount greater than 40, which were placed after 2/1/2004.

Use the proper formula in the cell I2, to calculate the sum of the Total of Sales, from the eastern district, with less than 55 units sold.

Share this Post