SUMIF with every condition type
SUMIF takes the condition as text: =SUMIF(A:A,"<>",B:B) sums where A is not blank, =SUMIF(A:A,DATE(2026,1,15),B:B) sums for one exact date, and =SUMIFS(B:B,A:A,">="&E1,A:A,"<="&E2) sums a date range. Summing by cell colour is the exception — no SUMIF variant can see formatting, so it needs a helper column or a script.
Sum where the criteria column is not blank
=SUMIF(A:A,"<>",B:B)The "<>" operator on its own means "not equal to nothing", i.e. not blank.
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
Sum where cells are not blank
The condition "<>" means "not equal to empty". It is the shortest way to say "only rows that have something in them".
Be careful with the difference between truly empty and looking empty. A cell holding "" — the empty string an IF often returns — is not blank as far as SUMIF is concerned, and "<>" will include it. If your source column is formula-driven, count on that difference biting you.
Criteria column is not blank
=SUMIF(A:A,"<>",B:B)Criteria column IS blank
=SUMIF(A:A,"",B:B)Not blank and not an empty string
=SUMIFS(B:B,A:A,"<>",A:A,"<>"&"")Excludes both truly empty cells and the "" that formulas return.
Sum where the value column itself has a number
=SUMIF(B:B,">0")When "not blank" really means "has a positive number", test the value column directly.
Sum for one exact date
A date in a spreadsheet is a number with a display format. Typing the date as text into SUMIF works only if the locale happens to agree with you, which is why the same formula behaves differently on two machines.
Build the date with DATE(year, month, day) and the ambiguity disappears. If the criteria column holds date-times rather than dates, an exact match will fail for every row that carries a time component — use a range covering the whole day instead.
One exact date, locale-proof
=SUMIF(A:A,DATE(2026,1,15),B:B)Preferred over "15/01/2026" as text, which depends on the machine locale.
Date held in another cell
=SUMIF(A:A,E1,B:B)Today only
=SUMIF(A:A,TODAY(),B:B)One calendar day, when the column holds date-times
=SUMIFS(B:B,A:A,">="&E1,A:A,"<"&E1+1)An exact match never fires against a timestamp. Bracketing the day does.
Correct, but depends on values from your own sheet, so we cannot run it here.
Sum across a date range
Two conditions means SUMIFS, not SUMIF — and the argument order flips. SUMIF takes the criteria range first; SUMIFS takes the sum range first. Getting this backwards is the single most common SUMIFS mistake.
The other trap is concatenation. An operator and a cell reference have to be joined with &: ">="&E1 works, ">=E1" is read as the literal text "E1".
Between two dates, inclusive
=SUMIFS(B:B,A:A,">="&E1,A:A,"<="&E2)Sum range first in SUMIFS. E1 is the start date, E2 the end date.
Correct, but depends on values from your own sheet, so we cannot run it here.
Current month to date
=SUMIFS(B:B,A:A,">="&EOMONTH(TODAY(),-1)+1,A:A,"<="&TODAY())Last 30 days
=SUMIFS(B:B,A:A,">="&TODAY()-30,A:A,"<="&TODAY())A date range and a category at once
=SUMIFS(B:B,A:A,">="&E1,A:A,"<="&E2,C:C,"Paid")Correct, but depends on values from your own sheet, so we cannot run it here.
Sum on partial text
SUMIF accepts wildcards in the criteria: * for any run of characters, ? for exactly one. This makes "contains", "starts with" and "ends with" straightforward.
Wildcards only apply to text criteria. They do nothing against numbers or dates.
Contains the word
=SUMIF(A:A,"*north*",B:B)Starts with
=SUMIF(A:A,"north*",B:B)Ends with
=SUMIF(A:A,"*north",B:B)Contains the text in a cell
=SUMIF(A:A,"*"&E1&"*",B:B)Literal asterisk or question mark
=SUMIF(A:A,"~*",B:B)A tilde escapes the wildcard so it is matched as an ordinary character.
Sum by cell colour — the honest answer
No SUMIF, SUMIFS or SUMPRODUCT formula can read a cell's fill colour. Formatting is not data, and the calculation engine has no access to it. Any page that shows you a =SUMIF(A:A,"red",B:B) is showing you something that does not work.
There are three real options. The best one is to stop relying on colour: whatever the colour means — overdue, priority, paid — put that meaning in a column, then SUMIF on the column. That also makes the sheet legible to anyone colourblind or reading it on a printout.
If the colours already exist and you cannot redo the sheet, Excel offers a legacy route through the old GET.CELL macro function used in a named range, or a short VBA function. Google Sheets has no formula route at all and needs Apps Script.
The pragmatic middle path: filter or sort by colour (both products support it in the UI), then read the SUBTOTAL of the visible rows.
The right fix — encode the meaning, then sum it
=SUMIF(C:C,"Overdue",B:B)Column C holds the status the colour was standing in for.
Shown as a mistake to avoid — do not use this one.
Sum only visible rows after filtering by colour
=SUBTOTAL(109,B2:B100)109 is "SUM, ignoring hidden rows". Filter by colour in the UI first, and this totals what remains.
When SUMIF returns 0
A zero result almost always means the criteria and the data are different types rather than different values. Numbers stored as text will not match a numeric criterion, and dates imported as text will not match a real date.
Check with =ISNUMBER(A2). If it returns FALSE for something that looks like a number or a date, the column needs converting before any SUMIF will work.
The second cause is mismatched range sizes. SUMIF is happy to accept a criteria range and a sum range of different heights and will silently line them up from the top, producing a wrong answer rather than an error.
Check whether the column is really numeric
=ISNUMBER(A2)Match text-formatted numbers
=SUMIF(A:A,"100",B:B)+SUMIF(A:A,100,B:B)A stopgap. Converting the column properly is the real fix.
Shown as a mistake to avoid — do not use this one.
SUMIF criteria syntax
| Condition | Criteria to write | Notes |
|---|---|---|
| Not blank | "<>" | Includes cells holding "" |
| Blank | "" | Truly empty cells only |
| Equals a number | 100 or "=100" | Both accepted |
| Greater than a cell | ">"&E1 | The & is required |
| Exact date | DATE(2026,1,15) | Locale-proof |
| Date range | Two conditions in SUMIFS | Sum range comes first |
| Contains text | "*north*" | Text criteria only |
| Not equal to | "<>Paid" | |
| Cell colour | Not possible | Needs a helper column or a script |
Frequently asked
How do I write "not blank" in SUMIF?
Use "<>" as the criteria: =SUMIF(A:A,"<>",B:B). Note that a cell containing an empty string "" returned by a formula counts as not blank.
Why does SUMIF with a date return 0?
Either the criteria date is text rather than a real date, or the column holds date-times and an exact match cannot fire. Build the date with DATE(2026,1,15), and bracket the day with SUMIFS if there is a time component.
Can SUMIF add cells by their fill colour?
No. No formula in Excel or Google Sheets can read cell formatting. Put the meaning behind the colour into a helper column and SUMIF on that, or filter by colour and use =SUBTOTAL(109,B2:B100).
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and takes the criteria range first. SUMIFS handles one or more and takes the sum range first. The reversed argument order is the most common source of errors when switching between them.
Do these SUMIF formulas work in Google Sheets?
Yes, with one caveat: Google Sheets is stricter about text criteria matching and does not support the legacy GET.CELL route for colours at all. Everything else on this page transfers unchanged.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.