Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to sum a range ignoring blanks in Excel and Google Sheets
Topic:SUM & aggregationExcel & Google Sheets
=SUMIF(A2:A6,"<>",B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Label | Value |
| Alpha | 100 |
| 50 | |
| Beta | 200 |
| Gamma | 80 |
| 30 |
=SUMIF(A2:A6,"<>",B2:B6)→380
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
Runs server-side · free · no signup
Step by step
- 1Decide which column gates the sum (the criteria column, e.g. column A — category, project code, assignee) and which column gets summed (e.g. column B — value).
- 2Enter =SUMIF(A2:A6,"<>",B2:B6). The "<>" criteria means "not equal to empty", so only rows with any value in column A are included.
- 3Press Enter. Rows where A is blank are excluded from the B total — even if B itself has a value.
- 4For multiple conditions (non-blank A AND non-blank C): use SUMIFS — =SUMIFS(B2:B6, A2:A6, "<>", C2:C6, "<>").
- 5For the inverse (sum where A IS blank): =SUMIF(A2:A6, "", B2:B6).
Tips
- Plain SUM ignores blanks in its own range. =SUM(B2:B6) where B5 is blank works fine — no SUMIF needed unless you're gating on a DIFFERENT column.
- "<>" excludes true blanks. Cells holding a formula that returns "" (zero-length string) are treated as non-blank by SUMIF in both Excel and Sheets — they have content (a formula), even if it displays as empty.
- To also exclude formula-returned empty strings: =SUMIFS(B2:B6, A2:A6, "<>", A2:A6, "<>\"\"") — though that pattern is unreliable across engines. Cleaner: clean the source data so blanks are truly blank.
- Count non-blank rows the same way: =COUNTIF(A2:A6, "<>") returns the count.
- Average of non-blanks: =AVERAGEIF(A2:A6, "<>", B2:B6) — same criteria pattern.
- If you need the SUM of B where B itself is non-blank: =SUMIF(B2:B6, "<>"). When the sum_range argument is omitted, SUMIF uses the criteria_range as the sum_range — a useful shortcut.
- Pre-365 Excel: SUMIFS exists; SUMIF only does single-criterion. Use SUMIFS for any AND condition even with one criterion if you want consistency across the workbook.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
What is the difference between "<>" and "<>"""?
In SUMIF criteria, "<>" and "<>""" are equivalent — both mean not equal to an empty string. The shorter "<>" is conventional and clearer.
How do I sum cells in B that are themselves non-blank?
=SUMIF(B2:B6, "<>") — only two arguments. When sum_range is omitted, SUMIF sums the criteria_range itself, so this sums non-blank cells in B. Or just use =SUM(B2:B6) since SUM already skips blanks; the SUMIF version only matters if you also want to exclude zeros: =SUMIF(B2:B6, "<>0").
Why does my SUMIF include rows where the gate cell looks empty?
That cell probably holds a formula that returns "" — like =IF(condition,"",X). SUMIF treats those as non-blank because they have content (a formula). Either replace those cells with truly empty cells (delete the formula), or wrap with SUMIFS adding ="" exclusion if your engine supports it.
How do I sum across two columns each non-blank?
Use SUMIFS with multiple criteria pairs: =SUMIFS(C2:C6, A2:A6, "<>", B2:B6, "<>"). SUMIFS uses AND logic — both conditions must be true.
How do I sum a column where ANOTHER column matches a specific value (not just non-blank)?
Standard SUMIF: =SUMIF(A2:A6, "Sales", B2:B6) sums B where A equals "Sales". Use wildcards for partial matches: =SUMIF(A2:A6, "*West*", B2:B6) sums where A contains "West".
Can I sum only blank rows (inverse)?
Yes — =SUMIF(A2:A6, "", B2:B6) sums B where A is blank. Useful for catching mis-categorized rows that should have a label but don't.
More on SUM & aggregation
See all →Reference
SUMPRODUCT
Multiply ranges together row by row and sum the result — ideal for weighted totals.
How-toHow to sum a column
Add up an entire column with SUM. Total a whole column or a fixed range — identical in Excel and Google Sheets.
Error fixFix 0
SUM returns 0 or the wrong total in Excel and Google Sheets — fix numbers stored as text, filtered ranges, manual calculation mode, and circular references.
ReferenceSUBTOTAL
Aggregate a range (sum, average, count…) while ignoring other SUBTOTALs and, optionally, hidden rows. Ideal under filters.
How-toHow to create a running total
Build a cumulative running total with a SUM and a clever anchored reference that grows as you copy it down. Works in both apps.
How-toHow to sum values by month
Total amounts that fall in a given month with SUMIFS and two date bounds. Works the same in Excel and Google Sheets.