SUMIFS adds up the rows that meet every condition you give it. That covers “this and that”. For “this or that” you need another approach, and the shortcut many guides suggest, a list of values in curly braces, didn’t work with SUMIFS in our tests: it added up only the first value, with no error. This guide shows what does work, with the result Google Sheets returned for each formula.
The examples use a short expense list in A1:E11:
| A: Date | B: Category | C: Payee | D: Method | E: Amount | |
|---|---|---|---|---|---|
| 2 | 2026-03-02 | Food | Grocer | Card | 54.20 |
| 3 | 2026-03-03 | Travel | Rail | Cash | 18.50 |
| 4 | 2026-03-05 | Food | Bakery | Cash | 6.80 |
| 5 | 2026-03-09 | Office | Paper Co | Card | 32.00 |
| 6 | 2026-03-12 | Food | Grocer | Card | 71.45 |
| 7 | 2026-03-15 | Travel | Taxi | Card | 24.00 |
| 8 | 2026-03-18 | Office | Printer Co | Transfer | 189.99 |
| 9 | 2026-03-21 | Food | Café | Cash | 12.30 |
| 10 | 2026-03-25 | Travel | Rail | Card | 18.50 |
| 11 | 2026-03-28 | Food | Grocer | Transfer | 63.10 |
Food adds up to 207.85, Travel to 61 and Office to 221.99. The whole list is 490.84.
Tested in Google Sheets. Every formula on this page was run on November 14, 2026, and the results below are the ones Google Sheets returned. How we test
AND: every condition must be true
=SUMIFS(E2:E11, B2:B11, "Food", D2:D11, "Card")
This returned 125.65: the two Food rows paid by card (54.20 + 71.45). The range to add comes first, then pairs of a range and its condition. Each extra pair narrows the result further:
| Conditions | Formula | Result |
|---|---|---|
| Food, over 50 | =SUMIFS(E2:E11, B2:B11, "Food", E2:E11, ">50") |
188.75 |
| Food, from March 10 on | =SUMIFS(E2:E11, B2:B11, "Food", A2:A11, ">="&DATE(2026,3,10)) |
146.85 |
| Everything except Food | =SUMIFS(E2:E11, B2:B11, "<>Food") |
282.99 |
| Food, not from Grocer | =SUMIFS(E2:E11, B2:B11, "Food", C2:C11, "<>Grocer") |
19.10 |
| Food, card, Grocer | =SUMIFS(E2:E11, B2:B11, "Food", D2:D11, "Card", C2:C11, "Grocer") |
125.65 |
| Category starting with “Fo” | =SUMIFS(E2:E11, B2:B11, "Fo*") |
207.85 |
The sum column can also carry a condition of its own, as in the “over 50” row. The operator goes inside the quotes; a date or a cell is joined with &. Our SUMIFS between two dates guide covers date ranges in detail.
Conditions from cells
With “Food” in N1 and “Card” in N2:
=SUMIFS(E2:E11, B2:B11, N1, D2:D11, N2)
returned 125.65. Change the cells and the total follows, which makes a small summary table easy to build.
OR within one column: Food or Travel
SUMIFS can’t do OR on its own: two conditions on the same column mean “Food and Travel at once”, and =SUMIFS(E2:E11, B2:B11, "Food", B2:B11, "Travel") returned 0. These versions returned the right total, 268.85 (207.85 + 61):
=SUMIF(B2:B11, "Food", E2:E11) + SUMIF(B2:B11, "Travel", E2:E11): two sums added together. Safe here because a row can’t be Food and Travel at the same time.=ARRAYFORMULA(SUM(SUMIF(B2:B11, {"Food","Travel"}, E2:E11))): one SUMIF with a list of values.=SUMPRODUCT((B2:B11="Food") + (B2:B11="Travel"), E2:E11)=SUMPRODUCT(REGEXMATCH(B2:B11, "^(Food|Travel)$") * E2:E11): handy for a long list of values.=SUM(MAP({"Food","Travel"}, LAMBDA(k, SUMIFS(E2:E11, B2:B11, k)))): runs SUMIFS once per value.
The curly-brace list doesn’t work with SUMIFS
| Formula | Result |
|---|---|
=ARRAYFORMULA(SUM(SUMIF(B2:B11, {"Food","Travel"}, E2:E11))) |
268.85 |
=ARRAYFORMULA(SUM(SUMIFS(E2:E11, B2:B11, {"Food","Travel"}))) |
207.85 |
The same with a vertical list, {"Food";"Travel"} |
207.85 |
=SUM(SUMIFS(E2:E11, B2:B11, {"Food","Travel"})), without ARRAYFORMULA |
207.85 |
=SUM(SUMIF(B2:B11, {"Food","Travel"}, E2:E11)), without ARRAYFORMULA |
207.85 |
Found in testing: with a list in braces, SUMIFS returned 207.85, the total for Food alone, even inside ARRAYFORMULA. Travel was silently left out. SUMIF with the same list worked, but only inside ARRAYFORMULA. =ARRAYFORMULA(SUMIFS(E2:E11, B2:B11, {"Food","Travel"})) returned a single value, 207.85, instead of one per category, so SUMIFS doesn’t expand the list at all. COUNTIFS behaved differently: =ARRAYFORMULA(SUM(COUNTIFS(B2:B11, {"Food","Travel"}))) returned 8, the correct count. Don’t assume a trick that works for counting also works for sums.
OR across two columns: Food or paid in cash
Here a row can meet both conditions, and that’s where totals get inflated. Two Food rows were paid in cash (6.80 and 12.30).
| Formula | Result |
|---|---|
=SUMIF(B2:B11, "Food", E2:E11) + SUMIF(D2:D11, "Cash", E2:E11) |
245.45 (wrong) |
=SUMPRODUCT(SIGN((B2:B11="Food") + (D2:D11="Cash")), E2:E11) |
226.35 |
Both SUMIFs, minus SUMIFS(E2:E11, B2:B11, "Food", D2:D11, "Cash") |
226.35 |
Adding two SUMIFs counted the two cash Food rows twice: 245.45 − 226.35 = 19.10, exactly their total. SIGN turns the sum of the conditions into 1 whether one or both are true, so each row counts once. Subtracting the overlap also works, but gets long with three conditions.
AND plus OR: Food paid by card or cash
The correct total is 144.75 (the card rows, 125.65, plus the cash rows, 19.10).
=SUMPRODUCT((B2:B11="Food") * ((D2:D11="Card") + (D2:D11="Cash")), E2:E11)returned 144.75. Multiply for AND, add for OR, and keep the OR part in its own brackets.=SUM(MAP({"Card","Cash"}, LAMBDA(m, SUMIFS(E2:E11, B2:B11, "Food", D2:D11, m))))returned 144.75.=SUMIFS(E2:E11, B2:B11, "Food", D2:D11, {"Card","Cash"})returned 125.65, and so did the same formula wrapped in ARRAYFORMULA(SUM(…)): only the card rows. It’s the same brace problem.
Other ways to get the same total
| Formula | Result |
|---|---|
=SUMPRODUCT((B2:B11="Food") * (D2:D11="Card"), E2:E11) |
125.65 |
=SUMPRODUCT((B2:B11="Food") * (D2:D11="Card") * E2:E11) |
125.65 |
=SUM(FILTER(E2:E11, B2:B11="Food", D2:D11="Card")) |
125.65 |
=QUERY(A1:E11, "select sum(E) where B='Food' and D='Card' label sum(E) ''", 1) |
125.65 |
=SUMIFS(E:E, B:B, "Food", D:D, "Card"), whole columns |
125.65 |
SUMIFS is the simplest when every condition is AND. FILTER has a catch: with no matching rows, =SUM(FILTER(E2:E11, B2:B11="Fuel")) returned #N/A, No matches are found in FILTER evaluation., where SUMIFS would return 0. QUERY compares text with case sensitivity; see our QUERY guide.
Mistakes that change the total
- Ranges of different sizes. With D2:D10 next to B2:B11, SUMIFS returned #VALUE!, Array arguments to SUMIFS are of different size. Every range must cover the same rows.
- Accents. Upper or lower case doesn’t matter, but accents do:
"Cafe"returned 0, while"café"found “Café” and returned 12.30. - Argument order. SUMIFS puts the range to add first; SUMIF puts it last. Mixing them up returns 0 without an error, as our date range tests showed.
- Numbers stored as text in the sum column are skipped by SUMIFS and by SUM. See convert text to numbers.
Our COUNTIFS and SUMIFS formula generator writes the AND version for you, with up to three conditions, and handles the quotes, the & and the end date of a range.