In Excel, use the not-blank criterion "<>" in functions such as COUNTIF, COUNTIFS, SUMIF, and SUMIFS. For example, with entries in A2:A20, =COUNTIF(A2:A20,"<>") counts cells Excel treats as nonblank; the same criterion can filter records or limit a calculation to rows with a value.
What “not blank” means in an Excel criterion
The operator <> means “not equal to.” In a criteria-based function, putting it in quotation marks as "<>" tells Excel to match cells that are not blank. The quotation marks are part of the criteria syntax, not characters to type into the worksheet.
This is different from checking whether a cell contains a particular word, number, or date. A criterion such as "North" matches the text North, while "<>" asks only whether the cell has something Excel considers present. As a result, a text value, a number, and a date can all meet the not-blank test.
“Not blank” also does not necessarily mean “useful.” A cell containing a space, a zero, or an error value is not necessarily an empty cell. If your real goal is to count meaningful text, positive numbers, or cells with visible characters, you may need a more specific test.
Count nonblank cells with COUNTIF
COUNTIF is the straightforward choice when you need a count based on one condition. With records in A2:A20, =COUNTIF(A2:A20,"<>") returns the number of cells in that range that meet the not-blank criterion. For example, if A2:A7 contains North, a genuinely empty cell, South, another genuinely empty cell, West, and East, the result is 4.
The range can be a row, column, or rectangular block. When using a block such as A2:C20, COUNTIF evaluates each cell in that block and counts the matches. If you need to count records rather than individual filled cells, use a single identifying column—such as an order number or customer name—so one record contributes at most one count.
To count only cells that meet multiple conditions, use COUNTIFS. With team names in B2:B8 and status values in C2:C8, =COUNTIFS(B2:B8,"North",C2:C8,"<>") counts rows where the team is North and the status cell is nonblank. If three North rows have a status and one North row has an empty status, the formula returns 3. Each criteria range must have the same dimensions.
Use not-blank criteria in SUMIF and SUMIFS
SUMIF can add values only when corresponding cells meet a condition. With amounts in C2:C6, =SUMIF(C2:C6,"<>",C2:C6) sums the nonblank entries in that amount range. If the cells contain 120, a genuinely empty cell, 80, 0, and 45, the result is 245: the zero is present but does not change the total. SUMIF ignores text when adding a numeric sum range.
In many workbooks, the condition belongs in one column and the numbers to add are in another. With categories in A2:A5, approval statuses in B2:B5, and amounts in C2:C5, =SUMIFS(C2:C5,A2:A5,"Travel",B2:B5,"Approved",C2:C5,"<>") adds amounts for Travel rows that are Approved and have a nonblank amount. If the matching rows contain 120 and 80, the result is 200. SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria.
Use SUMIF for one condition and SUMIFS when several conditions must all be met. The not-blank condition can apply to the value being summed, as in the example, or to another column that determines whether a row should be included. Check the ranges carefully: if a sum range is offset from its criteria range, Excel may return a result that is syntactically valid but does not reflect the intended records.
Formula results, spaces, and other tricky “blanks”
A cell that looks empty can have different underlying contents. It might be genuinely empty, contain a formula that returns an empty text string, or contain one or more spaces. Functions do not always treat these cases identically. In particular, COUNTA counts cells containing formulas even when their displayed result is an empty string, so it is not a reliable substitute for every not-blank count.
When a range includes formulas returning "", check the result against the behavior you need rather than assuming it matches a truly empty cell. If your definition is “has at least one character in its displayed text,” test the text length instead. With text or formula results in A2:A20 and no error values, =SUMPRODUCT(--(LEN(A2:A20)>0)) counts cells whose resulting text has a length greater than zero. A formula returning an empty string contributes zero; a cell containing a space contributes one or more unless you remove spaces first.
To ignore ordinary leading, trailing, or repeated spaces when counting text, use TRIM inside the length test. With values in A2:A20, =SUMPRODUCT(--(LEN(TRIM(A2:A20))>0)) counts entries that still have characters after TRIM removes extra standard spaces. This is useful for imported lists with cells that look blank but contain spaces. TRIM does not remove every possible invisible character, such as some nonbreaking spaces, and errors in the range can cause a formula based on LEN or TRIM to return an error.
Another common surprise is zero. A numeric 0 is a value, so it is not blank and meets the ordinary not-blank criterion. If you want to exclude zero as well as blanks, add a separate condition. With values in A2:A20, =COUNTIFS(A2:A20,"<>",A2:A20,"<>0") counts cells that are nonblank and not equal to zero. Decide whether text that looks like zero should count; Excel may handle text and numeric values differently depending on the data and comparison.
Apply a not-blank filter without writing a formula
If you want to view filled records rather than calculate a count or total, use AutoFilter. Select a cell in the data range, then choose Data > Filter. Open the dropdown for the column you want to check and clear the selection for (Blanks), leaving the nonblank entries selected. Excel hides rows that do not meet the filter; it does not delete them.
For a filter that explicitly selects nonempty cells, the dropdown may offer a “Nonblanks” option, depending on the range and Excel interface. If blank-looking formula results or whitespace are involved, inspect the filtered records because the meaning of blank depends on the underlying cell content. To show every record again, clear the filter from the column dropdown or use Data > Clear.
Filters are convenient for reviewing or copying visible records, but they do not replace a calculation. A formula continues to calculate from its referenced range even when some rows are hidden by a filter, unless the formula is specifically designed to respond to visible rows. Use a criteria function for a straightforward count or sum, and a filter when the main task is examining the matching records.
Choose the right test for the task
There is no single best definition of “not blank” for every worksheet. Pick a test based on what should qualify:
- Use
"<>"in COUNTIF, COUNTIFS, SUMIF, or SUMIFS when you need a standard criteria-based nonblank check. - Use LEN when a cell must produce text with at least one character, especially when formulas may return an empty string.
- Use TRIM with LEN when cells containing only ordinary spaces should count as empty.
- Add another criterion when zero, a particular status, or a specific category should be excluded.
- Use AutoFilter when you want to inspect or temporarily hide blank records instead of calculating a result.
For a direct check in a helper column, compare the cell with an empty string or test its length. With a value in A2, =IF(LEN(A2)>0,"Filled","Blank") labels the result as Filled when A2 produces at least one character, and Blank otherwise. This is often clearer than asking a colleague to interpret a more complex multi-condition formula.
Common errors and ways to troubleshoot them
The most frequent mistake is leaving out the quotation marks or changing the operator. In a criteria function, use "<>" for not blank, not "><". Also confirm that the condition is entered as a text criterion inside the formula, rather than typed into a worksheet cell and assumed to work as a special command.
If the count seems too high, check for cells containing spaces, values hidden by number formatting, or formula-generated results. A cell can display a dash or an apparently empty result because of its formatting even though it contains a value. Select the cell and inspect the formula bar to learn what is actually stored. If the count seems too low, verify that the selected range covers all relevant rows and that your expected records are not outside it.
Be cautious when copying formulas. In a formula such as COUNTIFS, criteria ranges need to line up row for row. If the data starts on row 2, a criteria range starting on row 3 can pair a status with the wrong person or transaction. For a growing list, an Excel Table can make ranges easier to maintain: select the data and choose Insert > Table, then build formulas using the table’s column references.
Finally, distinguish blank from missing or invalid data. A not-blank test says only that Excel sees content; it does not verify that a date is valid, that an amount is numeric, or that a required field has an acceptable value. For data-quality checks, combine the nonblank condition with the rule that describes acceptable content.
Frequently Asked Questions
What is the Excel criteria for not blank?
Use "<>" in a criteria-based function. For example, with entries in A2:A20, =COUNTIF(A2:A20,"<>") counts cells Excel treats as nonblank.
Does COUNTIF with "<>" count zero?
Yes. Zero is a value, not an empty cell. Add a second criterion such as "<>0" if you want to exclude zero as well as blanks.
How do I sum cells that are not blank?
With amounts in C2:C20, use =SUMIF(C2:C20,"<>",C2:C20). If another column controls which values qualify, use SUMIF or SUMIFS with that column as a criteria range.
How do I count cells that contain formulas returning an empty string?
First decide whether an empty-string result should count as blank. If you want to count only results with characters, use a LEN-based test such as =SUMPRODUCT(--(LEN(A2:A20)>0)), with results in A2:A20 and no errors in the range.
How do I ignore cells that contain only spaces?
Use TRIM with LEN, for example =SUMPRODUCT(--(LEN(TRIM(A2:A20))>0)). This ignores ordinary spaces but may not remove every invisible character introduced by copied or imported data.
Related
- Excel Invoice Bill Format: Build a Useful Template
- Excel Accounting Solver: What It Does and How to Use It
- Excel Budget Spreadsheet Free? Find Templates and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Flash Fill: Fill a Column Without a FormulaExcel Flash Fill is not a formula: it recognizes a pattern in examples you type and fills the…
- DAX Formula in Excel: How It Works and ExamplesA DAX formula in Excel is an expression used in the Excel Data Model to create a measure…
- Q1 and Q3 in Excel: A Formula for Finding QuartilesTo find the first and third quartiles in Excel, use QUARTILE.INC with quartile numbers 1 and 3. If…
- Excel IFS Default Value: Set a Reliable Fallback ResultExcel’s IFS function has no built-in default argument: if none of its tests is TRUE, it returns #N/A.…
- IFERROR in Excel: Leave Blank When a Formula ErrorsTo make an Excel formula show a blank instead of an error, wrap it in IFERROR and use…
- How to Find a Z Value in Excel: Formulas and ExamplesTo calculate a z-score for a value in Excel, subtract the dataset mean from the value and divide…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.