Forum Discussion
Counting a list with two criteria - date and value
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)) )
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))
)
- You're welcome!
GROUPBY (not yet widely available) can also make this task simple. The function's last argument essentially uses FILTER for its signature (Calculation speed tests with the two functions showed similar calc times).
This formula would return the counts for all months:
=LET( months, TEXT(Demo[Date], "mmmm"), NoZero, Demo[Val] <> 0, GROUPBY(months, Demo[Val], COUNT, , , , NoZero) )
