How to sum with multiple criteria (SUMIFS)

Sum values only when several conditions are all true.

The formula

How it works

SUMIFS takes the sum range first, then any number of range/criteria pairs — a row is included only when every condition matches. In the example below (region in A, product in B, amount in C) only rows 2 and 6 are both "East" and "Widget", so the result is 10 + 50 = 60. Criteria accept comparisons (">100"), wildcards ("Widg*") and cell references (">="&E1). Note the argument order differs from single-criterion SUMIF, where the sum range comes LAST — that swap is the single most common cause of a wrong or zero answer. SUMIFS combines its pairs with AND; the sections below cover OR logic in one column, criteria that sit on a row and a column at the same time, and mixing number, text and date tests in one formula.

“SUMIF with multiple criteria” — SUMIF takes one, SUMIFS takes many

There is no way to give SUMIF a second criterion: its signature is SUMIF(range, criterion, [sum_range]) — exactly one range and one criterion. The function you want is SUMIFS, which accepts unlimited range/criteria pairs and combines them with AND. The catch is the argument order: SUMIF puts the sum range LAST (and it is optional), while SUMIFS puts the sum range FIRST. Swapping them is the most common cause of a zero or nonsense total.

=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Widget")

SUMIF totals all three North rows (100 + 40 + 25). Adding the product test with SUMIFS drops the North/Gadget row and leaves 100 + 25. Written the SUMIF way round — =SUMIFS(A2:A7,"North",B2:B7,"Widget",C2:C7) — the formula is an error, not a wrong number, because SUMIFS reads the first argument as the range to add. Excel, Google Sheets and LibreOffice Calc all document the same signature and the same argument order.

Multiple criteria in the SAME column (OR logic)

Extra SUMIFS pairs narrow the result, so you cannot ask for “North OR South” by adding a second region pair — no row is both, and the answer is 0. Feed SUMIFS an array constant of the values instead: in Excel and LibreOffice it then returns one subtotal per value, and an outer SUM adds them up (Google Sheets is the exception — see the note under the table). SUMPRODUCT with added boolean tests is the equivalent that also works with a range of criteria in cells.

=SUM(SUMIFS(C2:C6,A2:A6,{"North","South"}))

Array-constant behaviour to know about: the braces hold literal values typed into the formula, separated by commas for a horizontal array — you cannot put a cell range inside them (use the SUMPRODUCT form, or SUMPRODUCT(SUMIFS(C2:C6,A2:A6,E2:E3)), if the wanted values live in cells). This pattern is only safe for OR over EXACT values in ONE column, because a row cannot equal two different values, so nothing is double-counted; OR across two different columns can overlap and needs the SUMPRODUCT form. Excel, Google Sheets and LibreOffice Calc all document the same brace syntax; in Google Sheets a semicolon separates rows and a comma separates columns, same as Excel. In pre-dynamic-array Excel the SUM(SUMIFS(...)) wrapper may need to be entered with Ctrl+Shift+Enter (documented); modern Excel takes it as a normal formula. Google Sheets is the real divergence here, and the executed column above shows it: =SUM(SUMIFS(C2:C6,A2:A6,{"North","South"})) returned 140 there, the North subtotal alone, against 220 in LibreOffice, and the (North OR South) AND Widget row returned 100 against 150. Google Sheets does no implicit array evaluation inside a scalar function’s arguments, so SUMIFS sees only the first value of the brace array. The SUMPRODUCT row above returned 220 in both engines, which makes it the portable choice; Wrapping it in ARRAYFORMULA does not rescue it: =ARRAYFORMULA(SUM(SUMIFS(C2:C6,A2:A6,{"North","South"}))) still returned 140, and the (North OR South) AND Widget form still returned 100, when executed in Google Sheets on 2026-08-30 — SUMIFS’s criteria argument is not array-expanded there even inside ARRAYFORMULA. Use the SUMPRODUCT row (220 in both engines), or SUMPRODUCT(SUMIFS(C2:C6,A2:A6,E2:E3)) when the wanted values live in cells.

Criteria on a column AND a row at the same time (2-D block)

When the labels run down the side AND across the top — regions in column A, quarters in row 1, numbers in the middle — SUMIFS cannot help: every criteria range it takes must be the same shape and orientation as the sum range, and a matrix has labels on two axes. SUMPRODUCT does it: multiply a vertical TRUE/FALSE mask for the rows by a horizontal mask for the columns, and the two broadcast into a rectangle of 1s and 0s over the block.

=SUMPRODUCT((A2:A4="South")*(B1:D1="Q2")*B2:D4)

Inside SUMPRODUCT, * is AND and + is OR, and the multiplication coerces TRUE/FALSE to 1/0. If you want the single intersecting cell rather than a sum, INDEX with two MATCHes is clearer: =INDEX(B2:D4,MATCH("South",A2:A4,0),MATCH("Q2",B1:D1,0)). If the block contains text or error cells the multiplication fails — unpivot the matrix into a flat region/quarter/amount list and go back to SUMIFS. The SUMPRODUCT syntax above is documented identically for Excel, Google Sheets and LibreOffice Calc.

Mixing number, text and date criteria in one SUMIFS

Criteria types can be mixed freely, and the same column may appear more than once — that is how you bracket a date range. Comparison operators live INSIDE the quotes; to compare against a cell or a function result, concatenate with & : ">="&DATE(2026,2,1) or ">="&E1.

=SUMIFS(C2:C6,B2:B6,"North",C2:C6,">=100",A2:A6,">="&DATE(2026,2,1),A2:A6,"<"&DATE(2026,4,1))

Column A holds real date values (shown here as yyyy-mm-dd; stored as date serial numbers), not text.

The four pairs narrow 1240 down to 250 + 500 = 750: the January row falls outside the window, the South row fails the region test, and the 90 row fails ">=100". Two points worth copying: the sum column can also be a criteria column (C appears as both), and an upper bound written as "<"&DATE(2026,4,1) is safer than "<="&DATE(2026,3,31) when the cells might carry a time component, which would otherwise push 31 March 09:00 out of the range. Dates must be real dates — text that merely looks like a date never matches. Excel, Google Sheets and LibreOffice Calc document the same operator-in-quotes and &-concatenation rules; only the DATE() serial epoch differs, and only for pre-1900 dates.

Wildcards, and what does (and doesn’t) carry across apps

For everything above, the syntax is identical in Microsoft Excel, Google Sheets and LibreOffice Calc as documented by each vendor — same argument order, same operators in quotes, same brace array constants, same SUMPRODUCT behaviour. Text criteria are case-insensitive in all three, and * (any run of characters) and ? (one character) work as wildcards.

=SUMIFS(C2:C5,A2:A5,"Widget*")

Things to watch. LibreOffice Calc can interpret criteria as regular expressions instead of wildcards (Tools > Options > Calc > Calculate); wildcards are the default for spreadsheets opened from .xlsx, which is the setting our run above used. Under the regex setting the same criterion is documented to be read as a regular expression matched against the whole cell, so "Widget*" would mean “Widge” followed by any number of t’s and would match none of these products. To match a literal * or ? put a tilde in front of it (~*). Google Sheets users often reach for FILTER or QUERY instead. FILTER is not Sheets-only: Excel 2021/365 and LibreOffice 24.8+ document it too, and the SUM(FILTER(...)) row above was executed here. Only Sheets’ FILTER takes several separate condition arguments, though — in Excel and LibreOffice Calc you multiply the conditions into one, as that row does (the multi-argument form returned #VALUE! when we ran it in LibreOffice 25.8.7.3). QUERY() is documented for Google Sheets alone, with no Excel or LibreOffice Calc equivalent, so keep to SUMIFS/SUMPRODUCT (or FILTER) if the file has to travel. Everything on this page was executed in LibreOffice Calc by our harness, and every formula in these tables went through Google Sheets too (Drive import, 2026-08-30) — the second column of each table is what Google returned, including the two rows where it disagrees. The Excel syntax comes from Microsoft’s official documentation; we do not run desktop Excel. Excel for the web we do run (OneDrive recalculation, 2026-09-01), but it is a different application from desktop Excel and its results live on the function pages.

Verified, not just documented

We ran =SUMIFS(C2:C6,A2:A6,"East",B2:B6,"Widget") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 60 — exactly the expected result. The 15 further formulas in the sections above were executed the same way, and the number shown beside each one is what LibreOffice actually returned — nothing on this page is a hand-typed result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 60 for the worked example, the same value LibreOffice produced. The 15 further formulas above were run through Sheets the same way and have their own column; 2 of them came back with a value different from LibreOffice’s, flagged in that column. A further 2 rows are Google Sheets alternatives: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for them. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

SUMIFS · SUMIF · SUM · SUMPRODUCT · ARRAYFORMULA · DATE · FILTER — see full Excel, Google Sheets & LibreOffice compatibility for each.

Related recipes

• How to SUMIF with OR criteria (this value or that one)
• How to reference a cell on another sheet
• How to sum a column that contains errors
• How to calculate a weighted average
• How to calculate a year-to-date (YTD) total

Related comparisons

• DATE vs DATEVALUE: build a date or parse one
• ARRAYFORMULA vs dynamic arrays: the same idea, three dialects
• FILTER vs QUERY: Sheets' two ways to slice data