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.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.