Count Non Blank Cells by Category in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To count non-blank cells by category in Excel, use the COUNTIFS function with a criteria range for the category and a second criteria range that counts cells that are not empty (using “<>” as the criteria). For example, =COUNTIFS(A2:A100, “Electronics”, B2:B100, “<>”) returns the number of Electronics rows where column B has any value.
Formula
The general formula is:
=COUNTIFS(category_range, category, check_range, “<>”)
- category_range: the range that contains the category labels (e.g., A2:A100).
- category: the specific category you want to count (e.g., “Electronics” or a cell reference like D1).
- check_range: the range where you want to count non-blank cells (e.g., B2:B100).
- “<>”: the criteria that means “not equal to nothing”, i.e., not blank.
The function counts each row where the category matches AND the check cell is not empty. It works with text, numbers, dates, and even formulas that return an empty string (though those are technically not blank).
Worked Example
Suppose you have a table with 100 rows. Column A lists categories (Electronics, Furniture, etc.) and column B has optional notes. You want to count how many Electronics rows have a note in column B.
Using the default values (100 rows, category “Electronics”, check column B), the formula would be:
=COUNTIFS(A2:A100, “Electronics”, B2:B100, “<>”)
If column B has notes in 15 of the Electronics rows, the result is 15. The calculator above lets you adjust the row count, category, and column letter to match your data, but it assumes the ranges start at row 2 and that the category column is A. For your actual data, you’ll replace those ranges with your own.
When to Use This Count
This technique is useful whenever you need to know how many records in a category have any data in a specific field. Common scenarios include:
- Counting how many customers in a region have provided a phone number.
- Counting how many products in a category have a discount applied.
- Counting how many tasks in a project have a completion date.
- Counting how many survey responses in a demographic group include a comment.
It’s different from counting all rows in a category (which would use COUNTIF) because it only counts rows where the second column is filled in.
How to Read the Result
The result is a whole number representing the count of matching rows. If you get 0, either there are no rows with that category, or all the check cells in that category are blank. If you get a number equal to the total category count, then every row in that category has a value in the check column.
Remember that the count is case-insensitive for text categories, so “electronics” and “ELECTRONICS” are treated the same.
Common Mistakes and Misconceptions
One frequent error is using COUNTIF with two criteria, which won’t work because COUNTIF only accepts one condition. You need COUNTIFS (with an S) for multiple criteria.
Another mistake is using “<>” as a criteria in the wrong range. The criteria applies to the check_range, not the category range. For the category, you put the actual category name or a cell reference.
A common misconception is that a cell with a formula that returns an empty string (“”) is blank. It is not. COUNTIFS with “<>” will count it because the cell contains a formula. If you need to exclude those, you’d need a more complex formula like SUMPRODUCT or a helper column.
Also, be careful with spaces. A cell that contains only a space character is not blank. You may need to use TRIM or clean your data first.
Alternatives to COUNTIFS
If you need to count non-blank cells by category across multiple columns, or if you have more complex conditions, consider these alternatives:
- SUMPRODUCT: =SUMPRODUCT((A2:A100=”Electronics”)*(B2:B100<>””)) works similarly and can handle array operations.
- PivotTable: Drag the category to Rows and the check column to Values, then set the value field to Count. It automatically excludes blanks by default.
- Filter and SUBTOTAL: Apply a filter on the category, then use =SUBTOTAL(3, B2:B100) to count visible non-blank cells.
- Power Query: For large datasets, you can group by category and count non-null values.
Each method has its own strengths; COUNTIFS is the simplest for a single category and a single column.
Frequently Asked Questions
How do I count non-blank cells by category using multiple categories?
You can add more criteria pairs to COUNTIFS. For example, to count rows where column A is “Electronics” and column B is “In Stock” and column C is not blank, use =COUNTIFS(A:A, “Electronics”, B:B, “In Stock”, C:C, “<>”).
Why does my COUNTIFS return 0 even though I see non-blank cells?
Check that the category name matches exactly (including spaces). Also, ensure the ranges are the same size. If you use A:A and B2:B100, the function may not work as expected. Use the same row range for all criteria ranges.
How do I count blank cells by category instead of non-blank?
Use COUNTIFS with the criteria “” (empty) for the check range: =COUNTIFS(A:A, “Electronics”, B:B, “”). This counts cells that are truly empty. Note that it will not count cells with formulas that return “”.
Can I count non-blank cells by category in Google Sheets?
Yes, the same COUNTIFS function works in Google Sheets with the same syntax. The formulas are identical.
What if my category is a number, like a product ID?
You can use the number directly in the formula, e.g., =COUNTIFS(A:A, 12345, B:B, “<>”). No quotes needed for numbers.
How do I count non-blank cells by category where the check column contains text or numbers?
The “<>” criteria counts any non-empty cell regardless of data type. It includes numbers, text, dates, and errors. If you want to count only numbers, use “>0” for numeric values, but that would exclude text.
Related
Formula used
in d | Data range rows | rows | 100 | 1 in c | Category | text | Electronics | - in col | Column to check | text | B | - out count | Non-blank count | count | 0 | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.