SUMIFS
What is it?
“The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria”. - Microsoft Office Support 2017
The difference between SUMIF and SUMIFS is the number of criteria that you would like the cells to meet. SUMIF meets a criterion while SUMIFS meets multiple criteria. If you missed my post on SUMIF, click here.
Excel Formula: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, [criteria2],...)
Note: any component of the formula that is in square brackets means it’s optional.
Formula Made Easy
Example
In my “Mad Hatter Chocolatier” data, I want to know HOW MANY ORDERS (sum) SMITH SUPERMARKET (criteria1) has placed that is DARK CHOCOLATE (criteria2).
Based on the above photo, if I were to fill in the cell references into my Formula Made Easy table, it will look like:
Formula Applied
Putting the above references into the Excel formula, it will be:
=SUMIFS(E:E,G:G,”Smith Supermarket”,I:I,”Dark chocolate”)
Alternatively, I can also replace “Smith Supermarket” & “Dark chocolate” with cell references: =SUMIFS(E:E,G:G,G2,I:I,I2).
Note when entering the criteria, it is not case-sensitive.
Download the file and give it a go here: Mad Hatter Chocolatier
Comments