Exceljet

Quick, clean, and to the point

Count non-blank cells by category

Excel formula: Count non-blank cells by category
Generic formula 
=COUNTIFS(range1,criteria1,range2,"<>")
Summary 

To count non-blank cells by category, you can use the COUNTIFS function. In the example show, the formula in G5 is:

=COUNTIFS(data[Group],F5,data[Done],"<>")

where data is an Excel Table in the range B5:D16. As the formula is copied down, it returns a count of non-blank dates by Group as seen in the worksheet.

Explanation 

In this example, the goal is to count non-blank dates in column D by group. All data is an Excel Table named "data" in the range B5:D16. This problem can be solved with the COUNTIFS function, as explained below.

COUNTIFS function

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS accepts ranges and criteria in pairs. For example, to count cells in A1:A10 that are equal to "red", you would use COUNTIFS like this:

=COUNTIFS(A1:A10,"red")

To count cells in the range A1:A10 that have a corresponding value in B1:B10 that is greater than 5, you would add another range/criteria pair like this:

=COUNTIFS(A1:A10,"red",B1:B10,">5")

Note: COUNTIFS is in a group of 8 functions that share a quirky syntax.

In the example shown, to count total names by group, we can use COUNTIFS like this:

=COUNTIFS(data[Group],"A") // returns 5

All data is in an Excel Table called named data and data[Group] is a structured reference that refers to the entire Group column.

Because there are 5 rows in the table where the group is "A", COUNTIFS returns 5. To extend the formula count names in group "A" where the Date value is not blank, we need to add another range/criteria pair like this:

=COUNTIFS(data[Group],"A",data[Date],"<>") // returns 4

For range2, we use data[Date] and for criteria2 we use "<>". The "<>" operator means "not equal to" and because we do not provide a value with the operator, it means "not empty". Because there are 4 rows in the table where the group is "A", and the Date is not empty, COUNTIFS returns 4.

To complete the problem, we need to replace the hard-coded "A" in the formula with a reference to cell F5. The final formula in cell G5, copied down, is:

=COUNTIFS(data[Group],F5,data[Date],"<>")

As the formula is copied down, it uses the Group values from column F to return a count of non-blank dates by group.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.