Count non-blank cells by category

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 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.