Syntax, a live playground, a measured multi-condition gallery, the sum_range-first trap, and a sourced error library: conditional sums with AND logic.
Adds the cells in sum_range that meet every criteria pair. The sum_range comes first, unlike SUMIF.
SUMIFS adds cells that meet every condition you give it. Each criteria pair is AND-ed with the others. Every row was measured live in HyperFormula on the sample table. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =SUMIFS(B2:B5,A2:A5,"Apple",D2:D5,"East") | 250 | Apple AND East: 100+150 |
| =SUMIFS(B2:B5,A2:A5,"Apple") | 250 | One criteria pair behaves like SUMIF |
| =SUMIFS(B2:B5,D2:D5,"East",C2:C5,">10") | 150 | East AND qty>10: only the 150 row |
| =SUMIFS(B2:B5,A2:A5,"Apple",B2:B5,">120") | 150 | Apple AND amount>120 |
| =SUMIFS(B2:B5,A2:A5,"*an*") | 200 | Wildcard matches Banana |
| =SUMIFS(B2:B5,A2:A5,"Apple",D2:D5,"West") | 0 | No Apple in West: 0, not an error |
| =SUMIFS(C2:C5,A2:A5,"Apple",D2:D5,"East") | 25 | Sum the qty column instead of amount |
| =SUMIFS(B2:B5,A2:A5,"<>Apple",D2:D5,"East") | 0 | Not-Apple AND East: none qualify |
| Expression | Result | Why |
|---|---|---|
| =SUMIF(range, criteria, [sum_range]) | sum_range last | One condition; sum_range optional |
| =SUMIFS(sum_range, range1, crit1, ...) | sum_range first | Many conditions; sum_range required |
Edit the table or type any formula — the engine evaluates instantly. Load a preset to see SUMIFS AND multiple criteria across columns, use wildcards, and return 0 when nothing matches.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
SUMIFS is available across modern Excel, Google Sheets and LibreOffice. It has been in Excel since 2007.
| Platform | Support | Notes | Gotcha |
|---|
SUMIFS is one piece. The full course walks the toolkit end to end with live, measured examples.