Summing Based on a Specific Criterion

If you read my last two posts on COUNTIF and COUNTIFS, you might wonder if, instead of just counting, you could add something based on specific criteria. Well, you can, with SUMIF!


SUMIF

With SUMIF, you can quickly create subtotals based on a single criterion. For instance, say you have bunch of spending data for the months of October, November, and December. Download the example.

You can use SUMIF to quickly add up your total spending for each month, even if your data was not in order. Let’s try it.

Set up a totals summary off to the side by entering your Months in column H.

Now in cell I2, enter this formula “=SUMIF(B2:B142,”OCTOBER”,D2:D142).”

This means look in the range B2:B142 for instances of “OCTOBER,” and then sum the numbers in the range D2:D142 for those rows. Note that you need to have quotes around the OCTOBER.

Looks like you spent $751.77 in the month of October.

Alternatively, you could also have typed H2 instead of “OCTOBER” in the formula.

The difference is that instead of checking for values equal to “OCTOBER” you are checking for values equal to whatever’s in H2. A subtle but important difference. If you want to be able to easily copy and paste your formula down to other cells, you should generally reference the cell address instead of typing out the word.

Additionally if you are planning on copying and pasting your formula, you should create absolute references. This will “lock” specific parts of your formula so they stay constant.

In our example, you will want dollar signs ($) in front of the numbers in B2:B142 and D2:D142, so that our formula looks like this:

Now copy and paste your formula from I2, to I3 and I4. Pretty slick right?

Stay tuned to find out how to do this with multiple criteria