With the cells to check in A2:A10 and the numbers to add in B2:B10, use =SUMIF(A2:A10,"<>",B2:B10) to sum values in column B only when the corresponding cell in column A is not blank. The criterion "<>" means “not equal to blank”; change the ranges to match your worksheet.
What “not blank” means in SUMIF
SUMIF adds numbers conditionally. Its syntax is SUMIF(range,criteria,[sum_range]): range is where Excel checks the condition, criteria defines what qualifies, and the optional sum_range contains the values to add. If you omit sum_range, Excel adds the cells in range itself.
The not-blank criterion is written as "<>". The angle brackets mean “not equal to,” and the empty text between them represents a blank. The quotation marks are part of the formula syntax. They do not mean that you should type the words “not blank” into the criterion.
For example, suppose A2:A6 contains the labels Paid, a blank cell, Paid, Pending, and a blank cell, while B2:B6 contains 120, 80, 45, 30, and 25. With that layout, =SUMIF(A2:A6,"<>",B2:B6) returns 195: Excel adds 120, 45, and 30 because those rows have a label in column A. The amounts beside blank labels are left out.
Sum a column only when its own cell is filled
If the cells you are checking are also the cells you want to add, use the same range for both arguments. For example, if the amounts are in B2:B10 and you want to ignore empty amount cells, the layout is B2:B10 for both the condition and the sum; use =SUMIF(B2:B10,"<>",B2:B10). Excel adds the nonblank numeric amounts in that range.
For this same-range case, the shorter formula =SUMIF(B2:B10,"<>") has the same result because the omitted third argument defaults to the condition range. Including the third argument can make a formula easier to read, especially for someone learning it, but it is not required.
Be clear about what “filled” refers to. If a row should count only when it has an order number, customer name, or status, check that column and sum the amount column. Checking the amount column instead answers a different question: whether the amount cell itself is nonblank.
Set up matching ranges correctly
When the condition range and sum range are different, their rows must line up. If A2:A100 holds project names and C2:C100 holds costs, checking A2:A100 and adding C2:C100 pairs each name with the cost on the same row. Both ranges should cover the same number of rows. Misaligned ranges can produce incorrect results even if the formula has no syntax error.
For a worksheet with status labels in A2:A6 and amounts in B2:B6, the formula =SUMIF(A2:A6,"<>",B2:B6) returns 195 when the example values above are used. If you instead check B2:B6, Excel will include amounts based on whether the amount cells are nonblank, not whether their status cells are filled.
For a long list that will grow, you can use a full-column reference. With labels in A:A and amounts in B:B, =SUMIF(A:A,"<>",B:B) checks the whole label column and sums the corresponding amount cells. Full-column references are convenient, though a bounded range or an Excel table can make a workbook’s structure easier to inspect.
Require more than one cell to be nonblank
Sometimes a row should qualify only when two fields are filled—for example, when it has both a customer and an invoice number. Use SUMIFS for multiple conditions. Its first argument is the sum range, followed by pairs of criteria ranges and criteria.
With customer names in A2:A100, invoice numbers in B2:B100, and amounts in C2:C100, use =SUMIFS(C2:C100,A2:A100,"<>",B2:B100,"<>"). This sums the amounts only for rows where both the customer and invoice cells are nonblank. A row must meet every condition supplied to SUMIFS.
To require just one field, use the simpler SUMIF formula rather than adding an unnecessary second condition. If you need to add values when either one field or another is filled, that is an “OR” condition, not the “AND” behavior of multiple SUMIFS criteria. Two separate conditional sums may be more understandable, but take care not to count a row twice when both fields are filled.
Truly empty cells, spaces, and formulas returning empty text
Spreadsheet cells that look empty are not always equivalent. A cell can be genuinely empty, contain one or more spaces, or contain a formula whose displayed result is an empty string such as "". These differences matter when a workbook imports data or uses formulas to hide results.
- Genuinely empty: The cell has no value or formula. This is the usual case handled by the not-blank criterion.
- A space or invisible character: The cell contains text, even if it looks empty on the screen. A space is generally treated as content, so the row may qualify as nonblank.
- A formula returning an empty string: The cell contains a formula even though it displays nothing. If your result depends on treating that output as blank, verify the formula against your data rather than assuming a visually empty cell is truly empty.
For a range that may contain formulas returning empty strings, a direct comparison can express the intended test. With A2:A10 holding the cells to check and B2:B10 holding numeric values, use =SUMPRODUCT((A2:A10<>"")*B2:B10). It compares each A cell with an empty string and adds the corresponding numeric B value only when the comparison is true. If the qualifying values in B are 10, 20, and 5, for example, the result is 35.
This SUMPRODUCT approach assumes the corresponding cells in B contain numbers or blanks. Text in B can cause an arithmetic error because the formula multiplies the condition by the values. If entries that look blank may contain spaces, clean or normalize the source data as appropriate; a space is not the same as an empty string.
Common errors and ways to check the result
A formula that returns zero often means no cells met the criterion, or that the ranges do not point to the intended columns. Check the condition range first: it must contain the cells whose presence or absence determines whether each row qualifies. Then check the sum range and confirm that the qualifying rows have numeric values there.
Other issues to look for include:
- Unequal or shifted ranges: Make sure the first row in the condition range corresponds to the first row in the sum range, and that both end on the intended row.
- Spaces in supposedly blank cells: A cell containing a space is text, not truly empty. Inspect suspect cells or clean the imported data.
- Numbers stored as text: SUMIF may not add text entries as numeric amounts. Convert values stored as text if they are meant to be numbers.
- Unexpected rows included: Check whether a formula returning an empty string or other invisible content is being treated as nonblank in your particular setup.
For a quick manual check, filter the condition column to show nonblank entries and compare the visible amounts with the formula result. You can also test a small sample range where you can calculate the expected total by hand. Filters do not change what SUMIF includes: the function evaluates its referenced range, including rows hidden by a filter.
Choose between SUMIF, SUMIFS, and other options
Use SUMIF when one condition controls the sum, such as “add the cost when the project name is not blank.” Use SUMIFS when every row must meet multiple conditions, such as having both a name and a date. Both are built-in Excel functions and are usually clearer than constructing a more complex expression for a straightforward total.
Use SUMPRODUCT when the condition needs a direct array comparison—for example, distinguishing formula results that display as empty text—or when the logic is more involved. It can be harder for a new spreadsheet user to interpret, so explain the formula or add a brief note if other people maintain the workbook.
An Excel table is a useful alternative to fixed cell ranges when records will be added regularly. Tables expand as new rows are entered, and their column names can make formulas easier to understand. You can create one by selecting the data and choosing Insert > Table, or by pressing Ctrl+T on Windows. On Mac, the shortcut is Command+T. Check that “My table has headers” is selected if the first row contains headings.
Frequently Asked Questions
What is the Excel formula for sum if not blank?
Use =SUMIF(A2:A10,"<>",B2:B10) when A2:A10 contains the cells to check and B2:B10 contains the values to add. The criterion "<>" means not blank.
How do I sum cells that are not blank in the same range?
If B2:B10 contains the values to check and add, use =SUMIF(B2:B10,"<>",B2:B10). You can omit the final range argument when it is the same as the first range.
Can I sum if another column is not blank?
Yes. Put the other column in the first argument and the numbers to total in the third. For example, check A2:A10 and sum B2:B10 with =SUMIF(A2:A10,"<>",B2:B10).
How do I sum only when two cells in a row are filled?
Use SUMIFS with a not-blank criterion for each required field. For example, with fields in A and B and amounts in C, use =SUMIFS(C2:C100,A2:A100,"<>",B2:B100,"<>").
Does SUMIF ignore cells containing a space?
No. A space is text, not a truly empty cell, so a cell containing a space can satisfy the not-blank test. Clean unwanted spaces if those cells should count as blank.
Will a filtered-out row be excluded from SUMIF?
No. SUMIF evaluates its referenced ranges regardless of whether rows are hidden by a filter. If you need a total that changes with filtered or hidden rows, use a subtotaling approach designed for visible data instead.
Related
- Excel NOW Date: How the Current Date and Time Work
- Excel Not Responding When Copying and Pasting? Fixes
- Excel Not Updating Links From Other Workbooks? Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUMIF: Sum Values When a Checkbox Is CheckedTo add only the values whose checkboxes are checked, put each checkbox’s TRUE/FALSE value in one column and…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- Excel Cell Protection Options: Lock Cells the Right WayExcel cell protection has three main parts: mark cells as Locked or Hidden, then use Protect Sheet to…
- VLOOKUP to Compare Two Columns on Different SheetsYes. In Excel for Microsoft 365, use an exact-match VLOOKUP to check whether each value in one sheet’s…
- Excel Formula for Number of Days Between Two DatesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Average in Excel Between Two Values: Formula & TipsTo average numbers between two values in Excel, use AVERAGEIFS with a lower-bound and an upper-bound condition. For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.