Excel COUNTIFS Not Blank: Multiple Criteria Guide

Formulas & functions · Updated November 22, 2026

To count non-blank cells with multiple criteria in Excel, use the COUNTIFS function with the criteria "<>" (meaning “not equal to blank”) for each column you want to require a value. For example, =COUNTIFS(A2:A100,"<>",B2:B100,"<>",C2:C100,"Yes") counts rows where column A is not blank, column B is not blank, and column C equals “Yes”.

Why COUNTIFS and Not Blank Can Be Tricky

Excel’s COUNTIFS counts cells that meet all the conditions you specify. The “not blank” criterion is written as "<>" in the formula. However, many users accidentally use "*" (asterisk) thinking it means “any text,” but that fails for numbers and dates. Another common mistake is using "<>"&"" which works, but the plain "<>" is simpler and more reliable. The key is that COUNTIFS treats a cell as blank if it contains nothing, an empty string from a formula (""), or a formula that returns an empty string. Cells with spaces, zero, or text like “N/A” are NOT blank.

How the Not Blank Criterion Works

In Excel, the comparison operator <> means “not equal to.” When you pair it with an empty string, "<>" effectively means “not equal to nothing,” i.e., not blank. This works for text, numbers, dates, and even errors (though errors will be counted as non-blank). For example, with data in A1:A5 containing 10, "", apple, 0, and a blank cell, =COUNTIF(A1:A5,"<>") returns 3 (10, apple, and 0 are counted; the empty string and blank are not). Note that a formula returning "" is considered blank by COUNTIFS, which is often desirable but can be surprising.

Using COUNTIFS with Multiple Criteria and Not Blank

To combine “not blank” with other conditions, simply add each criterion as a separate range/criteria pair. The syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). For instance, suppose you have a sales log with columns: Product (A2:A100), Region (B2:B100), and Sales Amount (C2:C100). To count rows where Product is not blank, Region is “West”, and Sales Amount is greater than 100, use:

=COUNTIFS(A2:A100,"<>",B2:B100,"West",C2:C100,">100")

This counts only rows that meet all three conditions. The ranges must be the same size, and the criteria are applied row by row.

Counting Non-Blank Cells Across Multiple Columns (AND Logic)

Sometimes you need to count rows where several specific columns are all non-blank. For example, count rows where both column A and column B have a value. Use:

=COUNTIFS(A2:A100,"<>",B2:B100,"<>")

This returns the number of rows where both A and B are non-blank. If you want to count rows where at least one of several columns is non-blank (OR logic), you cannot use COUNTIFS directly. Instead, use a SUMPRODUCT formula like =SUMPRODUCT(--((A2:A100<>"")+(B2:B100<>"")>0)). But for AND logic, COUNTIFS is the cleaner choice.

Common Problems and Solutions

  • Counting cells with formulas that return “”: These are considered blank. If you want to count them as non-blank, use SUMPRODUCT with LEN to check the actual cell length, e.g., =SUMPRODUCT(--(LEN(A2:A100)>0)).
  • Spaces that look blank: A cell with a space character (” “) is not blank. To treat it as blank, use =COUNTIFS(A2:A100,"<> ") (note the space inside the quotes) but this only excludes a single space. Better to clean data with TRIM.
  • Using wildcard characters: The asterisk (*) matches any sequence of characters, but it does NOT match a truly empty cell. So "*" will count cells with text but not numbers or dates. Avoid it for “not blank.”
  • Criteria as cell references: If you have the value “<>” in a cell (e.g., D1), you can use =COUNTIFS(A2:A100,D1) but ensure D1 contains exactly <> (without quotes).

Comparing COUNTIFS with SUMPRODUCT and FILTER

For simple AND conditions, COUNTIFS is fast and easy. For more complex logic (OR, or counting unique non-blank), consider SUMPRODUCT or the dynamic array function FILTER (available in Excel 2021 and Microsoft 365). For example, to count rows where A is non-blank and B is either “X” or “Y”, you could use =SUM(--(A2:A100<>"")*((B2:B100="X")+(B2:B100="Y")>0)) with SUMPRODUCT. The FILTER function can return the actual rows, and then you can use COUNTA on the result, but COUNTIFS is usually more efficient for just counting.

Frequently Asked Questions

How do I count non-blank cells in Excel with multiple criteria using wildcards?

Wildcards like * and ? work only with text criteria, not with the “<>” operator. To count non-blank text cells that also match a wildcard, use two criteria: =COUNTIFS(A2:A100,"<>",A2:A100,"A*") which counts non-blank cells starting with “A”. But for numbers, wildcards don’t apply.

Does COUNTIFS count cells with formulas that return empty text as blank?

Yes, if a formula returns an empty string (“”), COUNTIFS treats that cell as blank for the “<>” criterion. This is because the cell’s value is effectively nothing. If you need to count such cells as non-blank, use a SUMPRODUCT with LEN or ISFORMULA.

Can I use COUNTIFS with three or more “not blank” criteria?

Absolutely. Just add more range/criteria pairs, each with “<>“. For example, =COUNTIFS(A2:A100,"<>",B2:B100,"<>",C2:C100,"<>") counts rows where all three columns are non-blank. There is no limit to the number of pairs in COUNTIFS (up to 127 in modern Excel).

Why does my COUNTIFS return 0 even though I see values?

Check for invisible characters like spaces, line breaks, or non-breaking spaces. Use =LEN(A2) to see the character count. Also, ensure the criteria range and the criteria are correctly aligned, and that the ranges are the same size. If you copied data from the web, it might contain non-printable characters; use CLEAN and TRIM to clean.

Is there a difference between “<>” and “<>“&”” in COUNTIFS?

No, they are functionally identical. Both represent “not equal to empty string.” The shorter “<>” is preferred for readability. Some users think the second form is more explicit, but it does not change behavior.

Alternatives and Advanced Tips

If you need to count non-blank cells in a single column without criteria, use COUNTA. For multiple criteria with OR logic, consider using SUMPRODUCT with addition. For example, to count rows where either A or B is non-blank, use =SUMPRODUCT(--((A2:A100<>"")+(B2:B100<>"")>0)). For Excel 365 users, the FILTER function can return an array of rows that meet your criteria, and then ROWS(FILTER(...)) gives the count, but this is less efficient for large datasets. Always ensure your ranges are correctly sized and that you are not mixing data types unintentionally.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.