Spreadsheets

SUMIFS with OR conditions: total several categories in one formula

SUMIFS only does AND. Here is how to total several categories at once in Excel or Google Sheets with SUMPRODUCT and SUMIFS, ISNUMBER and MATCH, or a group column.

Editorial illustration for SUMIFS with OR conditions: total several categories in one formula

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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.

MethodBest whenWatch out for
Added SUMIFSTwo or three categoriesGets long
SUMPRODUCT(SUMIFS(... {array}))A fixed set you will not changeNames are typed inside the formula
SUMPRODUCT(SUMIFS(... range))A list you will editExtra empty cells in the list range; size it exactly to the list
SUMPRODUCT with MATCHSeveral tests at onceA 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.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports