Spreadsheets

SUMPRODUCT vs SUMIFS: which to use for expense totals

SUMPRODUCT or SUMIFS for expense totals? A side-by-side comparison on one sample sheet covering readability, text dates, speed and a simple decision rule.

Editorial illustration for SUMPRODUCT vs SUMIFS: which to use for expense totals

Use SUMIFS whenever your conditions are simple matches or date ranges, because it is shorter, easier to audit and fast. Switch to SUMPRODUCT only when the condition has to be calculated on each row, such as the month of a date, the day of the week or an OR across different columns. For a home expense sheet, SUMIFS covers most needs.

What does the same total look like in both?

Say column A holds dates, column B category and column C amount, and you want Groceries in March 2026. Hypothetical rows: ₹1,250 on 3 March, ₹980 on 12 March, ₹1,720 on 26 March and ₹860 on 2 April. The answer is ₹1,250 + ₹980 + ₹1,720 = ₹3,950.

  • SUMIFS: =SUMIFS($C$2:$C$500,$B$2:$B$500,"Groceries",$A$2:$A$500,">="&DATE(2026,3,1),$A$2:$A$500,"<"&DATE(2026,4,1))
  • SUMPRODUCT: =SUMPRODUCT(($B$2:$B$500="Groceries")*(MONTH($A$2:$A$500)=3)*(YEAR($A$2:$A$500)=2026)*$C$2:$C$500)

Both return ₹3,950. The SUMIFS version reads as a sentence: sum the amounts where the category is Groceries and the date is on or after 1 March and before 1 April.

How do they compare?

QuestionSUMIFSSUMPRODUCT
ReadabilityPairs of range and conditionBrackets and multiplication signs
Wildcards such as *swiggy*YesNo, needs SEARCH or ISNUMBER tricks
Test on a calculated value (MONTH, WEEKDAY)NoYes
OR across different columnsNeeds several SUMIFS addedPossible in one formula
Text in the amount columnIgnoredCan cause #VALUE! in the multiplication form
Ranges of unequal sizeErrorError
Works in Google SheetsYesYes

If you need the same total by month for a whole year, SUMIFS also copies across neatly: put the first day of each month in a header row and refer to it with the same "<"&EDATE(...) pattern. With SUMPRODUCT you would repeat the month and year tests in every cell.

Which one handles text dates better?

Neither handles them well. If dates are stored as text, SUMIFS will not match date criteria and MONTH inside SUMPRODUCT returns #VALUE!. Fix the dates first. Test a cell with =ISNUMBER(A2). If it says FALSE, the date is text and needs converting, for example with DATEVALUE or Text to Columns.

Is SUMPRODUCT slower than SUMIFS?

SUMPRODUCT does more work, because it builds a full list of results for every row before adding. On a few thousand rows of a personal expense log you are unlikely to notice any difference on a modern laptop. The gap can matter when a workbook has tens of thousands of rows and hundreds of SUMPRODUCT formulas, particularly if they use whole-column references. If your sheet starts to feel slow, replace SUMPRODUCT formulas with SUMIFS on a helper column, for example a Month column made with =TEXT(A2,"yyyy-mm") or a first-of-month date, rather than a calculation in every formula.

What is a simple rule for choosing?

  1. Can the conditions be written as equals, not equals, greater than, less than or a wildcard? Use SUMIFS.
  2. Do you need to test a part of a date, or the result of a calculation? Add a helper column and still use SUMIFS, or use SUMPRODUCT if you do not want an extra column.
  3. Do you need OR across columns in one cell? Use SUMPRODUCT.
  4. Is someone else going to read your sheet? Prefer SUMIFS.

There is also a maintenance angle. Six months from now you will open this sheet and need to remember what a formula does. A SUMIFS with a category cell and two date cells explains itself. A SUMPRODUCT with three brackets and a YEAR test needs a note beside it. If you do use SUMPRODUCT, add a comment in the neighbouring cell with the question the formula answers.

When a sheet stops being enough

Choosing between formulas is a sign your categories and dates are doing a lot of work. SpendixAI categorises imported statement rows and offers a library of ready-made reports, so the monthly totals do not depend on formulas that someone has to maintain.

What should I do next?

Build the SUMIFS version first, write the SUMPRODUCT version only for the question SUMIFS cannot answer, and keep both in a notes tab with a sample row so you remember what each one does.

SPENDIXAI JOURNAL

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

Explore your reports