Summing Based on Multiple Criteria

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

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.

data set

subtotal sectionSay 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.

sumifs formula

Neat, huh?

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).”

sumifs formula 2

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 🙂

filled formulaNow that you have your formula set, fill it down. You’re all set!

SUMIFS may just be the easiest way to create subtotals!

No comments yet.

Leave a Reply