Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum with multiple criteria in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Product | Sales |
| East | Widget | 500 |
| West | Widget | 300 |
| East | Gadget | 200 |
| East | Widget | 400 |
| West | Gadget | 150 |
=SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget")→900
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
- 1Identify the sum range (e.g. C2:C6 for sales amounts) and each criteria range with its matching value.
- 2Type =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget"). The first argument is always the sum range; criteria pairs follow in order.
- 3Press Enter. Only rows where column A = "East" AND column B = "Widget" contribute to the total.
Tips
- You can add up to 127 criteria pairs in SUMIFS.
- Use comparison operators in criteria strings: e.g. ">100" or "<>0".
- All ranges passed to SUMIFS must be the same size, or you will get a #VALUE! error.
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
What is the difference between SUMIF and SUMIFS?
SUMIF supports a single condition; SUMIFS supports multiple. Also note the argument order differs: SUMIF is (range, criteria, sum_range) while SUMIFS is (sum_range, criteria_range1, criteria1, ...).
Can I use OR logic with SUMIFS?
SUMIFS uses AND logic. For OR logic, add separate SUMIFS calls together: =SUMIFS(..."East"...)+SUMIFS(..."West"...).
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.