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
SUMPRODUCTwithLENto 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 withTRIM. - 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
- Java String Reverse: Methods and Examples
- String in Java: Meaning, Methods, and Examples
- Java String.join(): How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MATCH with Multiple Duplicates: How to HandleTo find the position of a value when duplicates exist, the MATCH function returns the first match by…
- HLOOKUP in Excel: How Horizontal Lookups WorkHLOOKUP in Excel searches across the first row of a range for a value, then returns a value…
- Excel Sort Dates by Month, Not by Year Across Multiple YearsTo sort Excel dates by month while ignoring the year, add a helper column with =MONTH(A2), fill it…
- Excel TODAY Formula for Days Between DatesTo calculate how many days have passed from a date in cell A2 to today, enter =TODAY()-A2 in…
- Excel MIN Function Examples: Find the Smallest ValueExcel’s MIN function returns the smallest number in a set of values. For example, with scores in B2:B8,…
- Find Common Names in Excel with Formulas and FormattingTo find names that appear in two Excel lists, use COUNTIF to check whether each name in the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.