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.
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: 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: 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: 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: 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: As the formula is copied down, it uses the Group values from column F to return a count of non-blank dates by group.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
