Sum if Not Blank in Excel (3 Easy Ways)

This calculator helps you sum cells in Excel only when they are not blank, using the SUMIF function with the criteria “<>” (not equal to blank). You enter a range to check for blanks and a range to sum, and the calculator returns the total of all cells in the sum range that correspond to non-blank cells in the check range.

Formula

The core formula is:

SUMIF(range, “<>”, [sum_range])

Where:

• range – the range of cells you want to test for blanks (e.g., A2:A10).
• “<>” – the criteria meaning “not equal to blank.” In Excel, this is the standard way to say “not blank.”
• [sum_range] – the range to sum. If omitted, Excel sums the range itself.

For example, =SUMIF(A2:A10, "<>", B2:B10) sums all values in B2:B10 where the corresponding cell in A2:A10 is not empty.

Worked Example

Suppose you have a list of sales in column A and amounts in column B, and you want to sum amounts only where column A has a value (not blank).

Using the default values from the calculator: check range A2:A10, sum range B2:B10. Let’s say the data is:

• A2: “Product A”, B2: 10
• A3: (blank), B3: 20
• A4: “Product B”, B4: 30
• A5: (blank), B5: 40
• A6: “Product C”, B6: 50
• A7: (blank), B7: 60
• A8: “Product D”, B8: 70
• A9: (blank), B9: 80
• A10: “Product E”, B10: 90

Non-blank cells in A2:A10 are A2, A4, A6, A8, A10. The corresponding amounts are 10, 30, 50, 70, 90. The sum is 10+30+50+70+90 = 250. The calculator returns 250, rounded to 2 decimals.

When to Use This

You use “sum if not blank” when you want to total only the rows that have meaningful data in a key column. Common situations:

• Summing sales only for products that have a name entered.
• Totaling hours only for employees who clocked in (non-blank time).
• Adding expenses only for rows where a receipt number exists.

It’s a way to exclude incomplete entries from your totals without manually filtering.

How to Read the Result

The result is the sum of all values in the sum range that correspond to non-blank cells in the check range. If you get 0, it could mean either all check cells are blank, or the sum range contains no numbers (or zeros). Remember that Excel treats cells with formulas that return “” (empty text) as not blank – they are not truly empty. If you want to ignore those too, you need a different approach (see Common Mistakes).

Common Mistakes and Misconceptions

• Using “<>” with SUMIF but forgetting the sum_range: If you write =SUMIF(A2:A10, "<>"), Excel sums the values in A2:A10 itself, not a separate column. Only omit sum_range when you want to sum the check range.
• Blank vs. empty text: A cell containing a formula that returns “” is not blank. SUMIF with “<>” will include it. To exclude those, use =SUMIFS(sum_range, check_range, "<>", check_range, "<>""") or use SUMPRODUCT.
• Spaces: A cell with a space character (” “) is not blank. It looks empty but is not. Use =SUMPRODUCT((TRIM(A2:A10)<>")*(B2:B10)) to ignore spaces.
• Using “<>” with SUMIFS: In SUMIFS, the criteria is the same: =SUMIFS(sum_range, check_range, "<>").

Alternatives to SUMIF

If you need more flexibility, consider:

• SUMIFS – allows multiple criteria. For example, sum where column A is not blank AND column C is “Yes”.
• SUMPRODUCT – can handle arrays and more complex conditions, like ignoring spaces: =SUMPRODUCT((A2:A10<>")*(B2:B10))
• FILTER function (Excel 365) – you can do =SUM(FILTER(B2:B10, A2:A10<>"")) which is more readable.

Frequently Asked Questions

How do I sum if not blank in Google Sheets?

Exactly the same: =SUMIF(A2:A10, "<>", B2:B10) works in Google Sheets as well.

What if my blank cells contain formulas that return “”?

SUMIF with “<>” will treat those as not blank. To exclude them, use =SUMIFS(B2:B10, A2:A10, "<>", A2:A10, "<>""") or =SUMPRODUCT((A2:A10<>")*(A2:A10<>"")*(B2:B10)).

Can I sum if not blank across multiple columns?

Yes, but you need to use SUMPRODUCT or array formulas. For example, to sum B2:B10 where A2:A10 is not blank and C2:C10 is not blank: =SUMPRODUCT((A2:A10<>")*(C2:C10<>")*B2:B10).

Why does my SUMIF return 0 when I see numbers in the cells?

Check if the numbers are stored as text. SUMIF ignores text values. Convert text to numbers using VALUE() or the Text-to-Columns wizard.

Is there a way to sum if not blank using a wildcard?

No, “<>” is the correct criteria. Wildcards like “*” will only match text, not numbers, and will ignore truly blank cells but also ignore numeric cells.

What does “<>” mean in Excel?

It means “not equal to”. In the context of SUMIF, “<>” with no value after it means “not equal to nothing” – i.e., not blank.

Related

• Mixed Number to Improper Fraction: How to Convert
• Volume Macintosh HD Was Found Corrupt? Fixes Explained
• WD Book Data Recovery: What It Means & How to Do It

in check | Check range (e.g., A2:A10) | - | A2:A10 | 0
in sum | Sum range (e.g., B2:B10) | - | B2:B10 | 0
out result | Sum of non-blank | - | SUMIF(check, "", sum) | 2