The SUMIFS function, adds all of its arguments that meet multiple criteria. First, let’s compare the SUMIF function. You use the SUMIF function to sum the values in a range that meet criteria that you specify. Suppose you have a column of numbers. You want to sum those numbers that are greater than 10 and ignore the others. This is s job for SUMIF.

In this Microsoft example, we want to know the number of products that begin with A and were sold by Tom. We have two criteria in this example, thats why we need to use the plural of the SUMIF() function, namely SUMIFS().

In the example below we want to sum the numbers in column B only if the product in column C starts with the letter “A” and the person in column D is “Tom”. We have two criteria in this formula.

The SUMIFS() function is good to know. It’s part of the Microsoft MO-201 exam (Microsoft Excel Expert (Office 2019)). It is helpful to understand how it works when you work with Power Pivot and the CALCULATE() DAX function because CALCULATE() is similar to SUMIFS(). It’s also helpful to compare it to the WHERE clause of a SQL SELECT statement, which is a fundamental part of the SELECT statement.

The SUMIFS() function is part of the ten functions you need to know in the YouTube video of Leila Gharani called Excel for Accounting – 10 Excel Functions You NEED to KNOW. Just for interest sake, here are her 10 functions: AGGREGATE, ROUND, EOMONTH, EDATE, WORKDAY, 3D, SUMIFS (and AVERAGEIFS, COUNTIFS), IF, VLOOKUP and TRIM. Note that Excel has a new function called XLOOKUP that replaces VLOOKUP, INDEX MATCH and more. Here is Leila’s video on XLOOKUP called How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples).

Database Functions that are Better

Looking for an even better way to accomplish the same thing? How about DSUM? Here is a YouTube video called The Excel Functions Almost Everyone Overlooks (Better Than SUMIFS). The video is by MyOnlineTrainingHub.