Spreadsheets

SUMPRODUCT to total expenses with multiple conditions

Use SUMPRODUCT to total expenses with several conditions, including OR logic and a MONTH test that SUMIFS cannot do, with correct Excel syntax.

Editorial illustration for SUMPRODUCT to total expenses with multiple conditions

SUMPRODUCT adds up amounts for rows where several tests are all true. Each test becomes a column of TRUE and FALSE values, you multiply the tests together, and multiply again by the amounts: =SUMPRODUCT(($B$2:$B$500="Food")*($C$2:$C$500="UPI")*$D$2:$D$500). It works in Excel and Google Sheets, and it can do things SUMIFS cannot, such as testing the month of a date.

How does the multiplication work?

Say your sheet has dates in column A, category in B, payment mode in C and amount in D. The test ($B$2:$B$500="Food") produces TRUE or FALSE for every row. Multiplying TRUE by TRUE gives 1 and anything involving FALSE gives 0, so the product is 1 only for rows meeting every test. Multiplying by the amount then keeps only those amounts, and SUMPRODUCT adds them. In plain terms, multiplication means AND.

How do I write OR instead of AND?

Add the tests instead of multiplying them, wrap the sum in brackets and multiply by the amounts. To total Food and Groceries together:

  • =SUMPRODUCT((($B$2:$B$500="Food")+($B$2:$B$500="Groceries"))*$D$2:$D$500)
  • Mixed conditions, such as Food or paid by UPI, can count a row twice when both are true. Convert the sum to 0 or 1 first: =SUMPRODUCT(--((($B$2:$B$500="Food")+($C$2:$C$500="UPI"))>0),$D$2:$D$500)

The double minus turns TRUE and FALSE into 1 and 0. Notice the comma before $D$2:$D$500 in the second formula. That comma form treats any text in the amount column as zero instead of failing.

How do I total one month across several years?

SUMIFS compares dates against a start and an end, but cannot ask what the month of each date is. SUMPRODUCT can. To total March spending in any year: =SUMPRODUCT(--(MONTH($A$2:$A$500)=3),$D$2:$D$500). To restrict to March 2026 add the year: =SUMPRODUCT((MONTH($A$2:$A$500)=3)*(YEAR($A$2:$A$500)=2026)*$D$2:$D$500).

Worked example with made-up rows:

DateCategoryModeAmount (₹)
4-Mar-2026FoodUPI640
11-Mar-2026FoodCard1,200
19-Mar-2026GroceriesUPI2,150
2-Apr-2026FoodUPI510

Food paid by UPI is ₹640 + ₹510 = ₹1,150 across the table. March Food paid by UPI is ₹640 only. March Food or Groceries is ₹640 + ₹1,200 + ₹2,150 = ₹3,990.

Can SUMPRODUCT use a cell for the category instead of typed text?

Yes, and it is better practice. Put the category in G1 and the month number in G2, then write =SUMPRODUCT(($B$2:$B$500=$G$1)*(MONTH($A$2:$A$500)=$G$2)*$D$2:$D$500). Now you can type Food and 3, or Fuel and 11, and read the answer without editing the formula. Typed text inside formulas is the main reason old sheets become hard to trust.

What goes wrong with SUMPRODUCT?

  • All arrays must be the same size. A range of rows 2 to 500 against rows 2 to 400 returns #VALUE!.
  • With the multiplication form, a text entry in the amount column such as "NA" produces #VALUE!. Use the comma form for the amounts, or clean the column.
  • Blank date cells are read as zero, which Excel treats as January 1900, so a month test for January can pick up empty rows. They add 0, so the total is fine, but a count would not be.
  • Do not use whole-column references like $A:$A inside SUMPRODUCT. It is slower because Excel works through over a million rows.

A practical use: tracking spending on days when you ate out and paid by card in the same month. Each extra condition is one more bracket multiplied in, so you can grow the formula one test at a time, checking the total after each. If the number jumps unexpectedly, the last bracket you added is the culprit.

When a sheet stops being enough

Layered conditions get hard to read after a few months. If you find yourself maintaining a dozen such formulas, SpendixAI has a Report builder where you filter and group transactions without writing formulas, and a CSV download for anything you want to analyse further in a sheet.

A short checklist

  • Real dates in column A, checked with ISNUMBER.
  • Same-size ranges, using fixed ends like row 500.
  • Tests wrapped in brackets before you multiply or add.
  • One total checked by hand against a filtered view.
SPENDIXAI JOURNAL

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

Explore your reports