Forum Discussion
Solved
Counting a list with two criteria - date and value
I have a table with a list of dates in column A and values in column B. I want to count the number of occurrences when column A is a specific value (such the month) and column B is greater than zero...
You need an array friendly function for this because the month name must be pulled from the dates. This rules out functions like COUNTIF/COUNTIFS because those functions want ranges. Nesting TEXT around the date column to pull the month name will break the formula.
Try this one:
=LET( months, TEXT(dates, "mmmm"), SUMPRODUCT((months = D2) * (Val <> 0)) )
It is still possible to use COUNTIFS, but separate checks are needed for the month start and end.
= COUNTIFS(
dates, ">=" & startDate#,
dates, "<=" & endDate#,
values, ">0"
)Another formula that I believe will be increasingly important in future is that offered by Patrick2788
= GROUPBY(
TEXT(dates,"mmm"),
IF(values, values),
COUNT,,
0)Whereas the Pivot Table tends to be part of the final presentation layer, GROUPBY and PIVOTBY fit easily into ongoing calculations.