This is kind of nerdy, but…well, I’m an accountant. It would be odd if it wasn’t nerdy. Most of the time when I’m using multiple criteria to add, my data is all going in the same direction. I’m using criteria only in columns or only in rows. SUMIFS is the perfect tool for that problem in Excel. What if I have criteria in both rows and columns? Is there a tool for that?
Typical Problem
Here is the typical problem with the typical solution.
| A | B | C | D | E | F | G | |
| 1 | MONTH | LOCATION | EMPLOYEE | SALES | |||
| 2 | January | Mitchell | Jeff | 1,235 | |||
| 3 | January | Sioux Falls | Joe | 987 | |||
| 4 | January | Mitchell | Mary | 2,352 | |||
| 5 | February | Mitchell | Jeff | 1,502 | |||
| 6 | February | Sioux Falls | Joe | 1,358 | |||
| 7 | February | Mitchell | Mary | 1,268 |
If I wanted a sum of all the Sales in Mitchell by Jeff, here is the formula:
=SUMIFS(D2:D7,B2:B7,”Mitchell”,C2:C7,”Jeff”)
My Problem
My problems was that I had a monthly budget that I wanted to use for reporting Year to Date amounts. Here’s the example:
| A | B | C | D | E | F | G | |
| 1 | 1/31/25 | 2/28/25 | 3/31/25 | 4/30/25 | 5/31/25 | 6/30/25 | |
| 2 | Mortgage | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 | 1,500 |
| 3 | Utilities | 350 | 350 | 300 | 250 | 250 | 200 |
| 4 | Groceries | 400 | 450 | 400 | 450 | 400 | 300 |
| 5 | Vacation | 0 | 0 | 0 | 0 | 0 | 1,800 |
In the video I show this more fully, but imagine you need the total of for Utilities year to date through April. What would you do?
=SUMIFS(B2:G5,A2:A5,”Utilities”,B1:G1,”<=DATE(2025,4,30)”) gives you an error. By the way, I used the date function, but typically I would just have a cell (like A1) where I plug in the date.
Try SUMPRODUCT:
=SUMPRODUCT((B2:G5)*(A2:A5=”Utilities”)*(B1:G1<=DATE(2025,4,30)))
I’ve been using this function for 20 years but still don’t understand it all the way. Here is the general format: =SUMPRODUCT((Sum Range)*(Criteria Range & Criteria)*(Criteria Range 2 & Criteria 2))
You can add as many criteria as you want. It works pretty slick.