Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum by both row and column criteria in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIFS(C2:C9,A2:A9,A11,B2:B9,B11)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Structure your data as a flat table: column A for the row dimension (e.g. Region), column B for the column dimension (e.g. Product), column C for the values.
- 2In A11 enter the region to match (e.g. North) and in B11 enter the product (e.g. Widget).
- 3In C11 enter =SUMIFS(C2:C9,A2:A9,A11,B2:B9,B11). This sums all C values where A matches A11 AND B matches B11.
- 4To build a full cross-tab, place unique region names in E2:E4 and unique product names in F1:H1. In F2 enter =SUMIFS($C$2:$C$9,$A$2:$A$9,$E2,$B$2:$B$9,F$1) with mixed references, then copy across and down.
- 5Add a Grand Total with =SUM(C2:C9) and verify the cross-tab rows and columns sum to the same total.
Tips
- All ranges in SUMIFS must be the same size — mismatched ranges return a #VALUE! error with no further explanation.
- Wildcard matching (e.g. criteria "North*") works in SUMIFS. SUMPRODUCT does not support wildcards but handles Boolean array logic that SUMIFS cannot.
- SUMPRODUCT is the reliable fallback for Excel 2010 and earlier, which lack SUMIFS, and for any scenario where criteria are dynamically computed.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I sum across a true matrix where headers are in row 1 and column A?
Use INDEX+MATCH to locate the intersection cell directly, or SUMPRODUCT with row/column index arrays. Restructuring the data to a flat table first is usually the simpler long-term choice.
SUMIFS gives zero but I know there are matching rows — why?
Check for leading or trailing spaces in criteria cells (use TRIM) and confirm data types match — a number stored as text does not equal a number.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.