Have you ever wanted to add up only certain numbers within a list? Maybe it was sales over a certain dollar amount. Or you you have a list of payments and you want to know the total of all payments with a payment type of “Check”. If you only have one criteria for selecting the numbers to add, you can use Excel’s SUMIF function. But if you have multiple criteria, you need SUMIFS.
The format for the SUMIFS function is fairly simple: =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2).
Sum_Range is the range of cells that the numbers to be summed will come from.
Criteria_Range1 is the range cells to look in for the first condition that must be meet in order for a number in the sum range to be included in the sum.
Criteria1 is the criteria that Excel will look for in Criteria_Range1.
You can have up to 127 criteria (although I hope for your sake that you never need that many)
Even though the format is simple I prefer using the Insert Function wizard. The wizard’s fill-in-the-blank interface makes it easier to keep track of which argument you are entering and it shows you the results as you go. And it automatically surrounds criteria text in quotation marks for you.
Let’s use this list of payments for a couple of examples.
| A | B | C | D | |
| 1 | Date | Payment Method | Payee | Amount |
| 2 | 3/7/19 | Check | Gwinnett County Water | 73.42 |
| 3 | 3/9/19 | EFT | Hubbard Wholesale | 1,500.00 |
| 4 | 3/12/19 | Card | Clooney Chemical Supply | 124.68 |
| 5 | 3/18/19 | Check | Poole, Gene | 200.00 |
| 6 | 3/25/19 | EFT | Mills Leasing Corp. | 550.00 |
| 7 | 3/30/19 | Card | Daniel Lawn Pro, Inc. | 335.50 |
| 8 | 4/1/19 | Check | Gene Poole | 450.00 |
| 9 | 4/13/19 | EFT | Akerson Distribution | 1,000.00 |
| 10 | 4/14/19 | Card | Mr. Gene Poole | 147.00 |
| 11 | ||||
| 12 | 4,380.60 | |||
| 13 | ||||
| 14 | EFT | 2,500.00 | ||
| 15 | Mr Poole | 650.00 |
Cell D14 is summing all payments greater than or equal to $1,000 with a payment method of EFT. Here are two variations on the formula.
=SUMIFS(D2:D10,D2:D10,”>=1000″,B2:B10,”EFT”)
This is saying: sum any number from cell D2 to D10 if the number in cell D2 to D10 is greater than 1000, AND if the cell on the corresponding row in column B contains “EFT”.
Here’s a variation that gets the same result using a slightly different method.
=SUMIFS(D2:D10,D2:D10,”>1000″,B2:B10,C14)
Here, instead of specifically telling Excel to look for “EFT” in cells B2 through B10, we tell it to look for whatever is in cell C14. Since C14 is not surrounded with quotation marks, Excel interprets it as a cell reference instead of as text to be matched.
In cell D15 we totaled all checks to Mr. Poole. But we know that our data entry people aren’t always consistent with how they enter names,so we wanted to sum any cell in D2 through D10 when column B exactly equals “Check” and column C contains “Poole” anywhere within the cell contents. That formula looks like this:
=SUMIFS(D2:D10,B2:B10,”Check”,C2:C10,”*Poole*”)
You can see that there is an * before and after Poole. That means there can by any text before and/or after Poole. “*Poole” would look for text ending with Poole and “Poole*” would look for text beginning with Poole.
