Summing Based on Multiple Criteria
by Janet Dagostino, Savvy Spreadsheets
In the last post I showed you how to use SUMIF, which let’s you subtotal based on a specific single criterion. You can also subtotal based on multiple criteria with a very similar, but much more powerful function called SUMIFS.
SUMIFS is structurally similar to SUMIF, but you put your sum range in the beginning as opposed to the end. Let’s try an example with the same data set we’ve been using.
This is hypothetical spending data from the October through November.
Say we want to subtotal all our spending by month AND by category. SUMIFS is the perfect tool for the job. First create this section in columns H-J.
In J2, you will want to type this formula “=SUMIFS(D2:D142,B2:B142,”OCTOBER”,E2:E142,”CLOTHING”).” D2:D142 is the section you are summing, B2:B142 is your first criteria range, “OCTOBER” is your first criteria, E2:E142 is your second criteria range, and “CLOTHING” is your second criteria. So the formula is looking for OCTOBER in B2:B142, and CLOTHING in E2:E142, and whenever those criteria are met, it will add the number in D2:142.
Now if we want to copy this formula from J2 all the way down to J22, we first want to make a couple of adjustments to our formula. Change it to “=SUMIFS(D$2:D$142,B$2:B$142,H2,E$2:E$142,I2).”
We want to put a “$” in front of some of our numbers so they stay constant when we copy our formula down. The “$” makes something absolute.
The other thing we did was that we replaced “OCTOBER” with H2 and “CLOTHING” with I2. In SUMIFS, our criteria can be either an actual value, like “OCTOBER”, or it can refer to a cell address, like H2. Just remember that if your criteria is going to be a text value, it needs to be in quotes. Cell references are better to use if you are copying the formula to another location, since the references will update automatically
Now that you have your formula set, fill it down. You’re all set!
SUMIFS may just be the easiest way to create subtotals!