SUMIFS combines its conditions with AND, so asking it for Food and Dining in the same column returns zero, because no row is both. To get Food or Dining or Groceries, wrap SUMIFS around an array of categories and add up the pieces: =SUMPRODUCT(SUMIFS($C$2:$C$500,$B$2:$B$500,{"Food","Dining","Groceries"})). It works in Excel and in Google Sheets.
Why does SUMIFS alone not do OR?
Every pair of range and criterion you add must be true for a row to count. Two criteria on the same column are therefore contradictory. Many people discover this when their formula returns 0 and they assume the data is wrong.
Which method should I use?
Take amounts in column C, categories in column B and rows 2 to 500. Here are four methods; the first two are the simplest.
- Add separate SUMIFS: =SUMIFS($C$2:$C$500,$B$2:$B$500,"Food")+SUMIFS($C$2:$C$500,$B$2:$B$500,"Dining")+SUMIFS($C$2:$C$500,$B$2:$B$500,"Groceries"). Long but easy to read. Fine for two or three categories.
- SUMIFS with an array constant: =SUMPRODUCT(SUMIFS($C$2:$C$500,$B$2:$B$500,{"Food","Dining","Groceries"})). SUMIFS returns one total per name and SUMPRODUCT adds them. In Google Sheets, SUMPRODUCT also makes the array calculate; =SUM(SUMIFS(...)) on its own may not.
- A list in cells: put the category names in F2:F4 and write =SUMPRODUCT(SUMIFS($C$2:$C$500,$B$2:$B$500,$F$2:$F$4)). To add a category, add a name below the list and extend the range.
- MATCH inside SUMPRODUCT: =SUMPRODUCT($C$2:$C$500*ISNUMBER(MATCH($B$2:$B$500,$F$2:$F$4,0))). MATCH asks whether each row's category appears in the list, ISNUMBER turns the answer into TRUE or FALSE, and multiplying by the amount keeps only the matching rows.
A worked check
Say Food is ₹8,200, Dining ₹3,400 and Groceries ₹6,100 across the rows. Each method should give 8,200 plus 3,400 plus 6,100, which is ₹17,700. If one gives a different answer, compare it with the first method; that is your cross-check.
| Method | Best when | Watch out for |
|---|---|---|
| Added SUMIFS | Two or three categories | Gets long |
| SUMPRODUCT(SUMIFS(... {array})) | A fixed set you will not change | Names are typed inside the formula |
| SUMPRODUCT(SUMIFS(... range)) | A list you will edit | Extra empty cells in the list range; size it exactly to the list |
| SUMPRODUCT with MATCH | Several tests at once | A text cell in the amount column gives #VALUE! |
Can I do OR between different columns?
Yes, but the pieces overlap. To total rows where Category is Food or Paid by is Cash, add the two SUMIFS and subtract the rows that satisfy both: =SUMIFS(C,B,"Food")+SUMIFS(C,D,"Cash")-SUMIFS(C,B,"Food",D,"Cash"), using the full ranges from the earlier formulas in place of C, B and D. If you skip the subtraction, Food paid in cash is counted twice.
Can wildcards or exclusions replace the list?
Sometimes. If your categories are named with a shared start, such as Food - Groceries, Food - Dining and Food - Delivery, a wildcard picks them all up: =SUMIFS($C$2:$C$500,$B$2:$B$500,"Food*"). The asterisk stands for any characters. And if you want everything except one category, flip the logic: =SUMIFS($C$2:$C$500,$B$2:$B$500,"<>Rent") totals every row that is not Rent. Both stay inside SUMIFS, so there is no array to worry about.
Is there a simpler way than a long formula?
Often, yes. Add a Group column beside Category that says Eating out for Food, Dining and Groceries, using a small mapping table and a lookup. Then =SUMIFS($C$2:$C$500,$E$2:$E$500,"Eating out") is an ordinary one-condition formula that anyone can read. The mapping table is also the place to change your mind later.
Where does an app help?
Grouping is something categories are for. In SpendixAI you can create custom categories and sub-categories, and reports are grouped by category, so a combined Eating out figure does not need a formula.
Checklist
- Use SUMIFS for AND, SUMPRODUCT with SUMIFS for OR.
- Keep ranges the same size.
- Compare two methods on a month you know.
- Keep category names spelt the same everywhere.
- Consider a Group column once the list grows.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


