SUMIF Generator

How SUMIF works

Availability: Excel 97 onwards, Excel for Mac, Excel for the web, Google Sheets and LibreOffice Calc. The third argument behaves identically everywhere.

SUMIF tests one range against one condition and adds up the corresponding cells in another. The argument order is the awkward part: the range being tested comes first, the condition second, and the range being added last - the reverse of SUMIFS, where the sum range leads. Leave the third argument off and SUMIF adds the tested range itself, which is what you want for a plain =SUMIF(B2:B200, ">100").

Criteria are more flexible than they look. A bare value matches on equality. A quoted comparison such as ">100", "<=0" or "<>Cancelled" applies an operator. "" matches genuinely empty cells and cells holding a zero-length string from a formula; "<>" matches everything that is not blank. Text criteria accept the wildcards * for any run of characters, ? for a single character, and ~ to escape a literal asterisk or question mark, so "Ltd*" catches every company name starting with Ltd. Matching text is case-insensitive: "paid", "Paid" and "PAID" are one criterion.

To compare against a cell rather than a typed value, the operator has to be joined to the reference with an ampersand: ">"&F1. Writing ">F1" compares each cell against the literal three-character text F1, which normally matches nothing and returns 0 with no complaint at all. The same rule applies to dates: ">="&DATE(2026,1,1) works, ">=01/01/2026" is fragile because it depends on the machine's date settings.

The one behaviour that surprises people is how sum_range is sized. Excel does not require it to match the criteria range - it takes only sum_range's top-left cell and then stretches a block the same height and width as the criteria range. So =SUMIF(A2:A100, "West", C2) quietly adds C2:C100, and =SUMIF(A2:A100, "West", C2:C50) adds C2:C100 as well, including 50 rows you never named. It works, until the columns are not the same length and it starts adding the wrong rows. Enter both ranges at the same size every time.

Syntax

More worked examples

Common mistakes

Swapping SUMIF's argument order with SUMIFS'

=SUMIF(D2:D500, "Travel", B2:B500) tests the amounts column for the word Travel, finds nothing and returns 0 - a plausible-looking number, not an error. SUMIF is range, criteria, sum_range; SUMIFS is sum_range, then pairs.

Comparing against a cell without the ampersand

">F1" is text, not a reference. It matches nothing and returns 0. The operator must be a quoted string joined to the reference: ">"&F1. This catches people most often on dates and thresholds stored in a settings cell.

A mis-sized sum_range that still returns a number

Excel resizes sum_range from its top-left cell to match range, so a mismatch does not error - it just adds a different block of cells than you named. Two ranges of different heights is the usual cause of a total that is close but not right.

Numbers stored as text never matching a numeric criterion

">100" tests numbers. A column imported as text looks identical on screen but matches nothing, so the total comes back 0. Check with =ISNUMBER(B2) and convert the column before summing.

Trying to build an OR condition

SUMIF takes exactly one criterion. For "Travel or Subsistence", add two SUMIFs together, or use =SUM(SUMIF(B2:B500,{"Travel","Subsistence"},D2:D500)) - the array constant runs the function once per item and SUM adds the pair. SUMIFS does not help here; its criteria are always AND-ed.