Recall that IF statement? Of course you do. Remember that any IF statement is conditional – meaning its checking values, assessing whether a certain condition is met, then passing a result. Excel and Google Sheets have many additional formulas that use the conditional IF. One that you’ll probably find quite useful in your analysis is the SUMIF variety.
Essentially, what’s happening is you’re telling excel, sum this set of values, BUT, and it’s a big but, only sum them IF a certain condition is met. The condition could be anything, and it’s up to you to define – it could be a numeric value, or a categorical value.
Here’s how the formula breaks down…
=SUMIF([range of cells to check for a criteria], [criteria, or the condition to check for], [the values you want to sum if that criteria is true])
If we took the following dataset and decided we want to sum the total sales only for quarters where total sales exceeded $500M – we could use a SUMIF! We’ll explore two applications in this example.
| A | B | C | |
| 1 | Quarter | Total Sales ($m) | Quarter above $500m |
| 2 | Q1 | $450 | N |
| 3 | Q1 | $505 | Y |
| 4 | Q3 | $550 | Y |
| 5 | Q4 | $450 | N |
Example 1: Using the numerical values with SUMIF
In this case, we are going to use the actual values as the criteria to determine whether or not to sum the values. The formula is =SUMIF(B2:B5, “>500”, B2:B5). You’ll notice that our criteria in this case is self-contained in the values that we ultimately want to sum. The “>500” is telling excel to look for any value greater than 500 as the criteria, and only sum those values that meet that criteria. With this formula, we get $1,055 from $550+$505.
Example 2: Using the categorical values with SUMIF
The other way to leverage the power of SUMIF is through categorical values, or anything that’s not a number. Looking back to the same dataset, we’ve got a column that categorizes quarters above or below $500M in sales with a simple “Y” or “N.” We can alter our formula to =SUMIF(C2:C5, “Y”, B2:B5) to get to the same result. Now we are checking the criteria “Y” and when that condition is met, summing the corresponding values. This delivers the same outcome, $1,055 by summing $550 and $505.
Now, let’s get real crazy. You may run into a scenario where you want to sum a set of values, but you have multiple sets of criteria. Fear not friend, Bill (Gates) thought of that. SUMIFS follows the same rules as SUMIF, but it gives you the opportunity to use multiple criteria in your selection. Consider the following dataset…
| A | B | C | D | |
| 1 | State | Region | Quarter | Sales ($m) |
| 2 | NY | Northeast | Q1 | 200 |
| 3 | NY | Northeast | Q2 | 100 |
| 4 | CA | West | Q1 | 300 |
| 5 | CA | West | Q1 | 200 |
| 6 | ME | Northeast | Q1 | 50 |
| 7 | ME | Northeast | Q1 | 25 |
Now if we wanted to know Q2 Sales in the Northeast, we could answer that question with a SUMIFS since we have two separate categorical values that will serve as our criteria. Generically, the formula for SUMIFS is slightly different.
=SUMIFS([values you want to sum assuming all criteria are met], [criteria range 1], [criteria 1], [criteria range 2], [criteria 2], …)
Applied to our example, the formula is =SUMIFS(D2:D7, B2:B7, “Northeast”, C2:C7, “Q2”). We’re saying to excel, sum those values in D2:D7, but hey, only do that if two conditions are met – if our value in B2:B7 is “Northeast” and if our value in C2:C7 is “Q2.” And excel says – you got it.
These are simple examples, but imagine the power of SUMIF when you’ve got hundreds of rows of data to work with. Have you found great use cases for SUMIF? Tell us about them!
