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)) )
With a little tweaking, this worked great. Thank you.
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) )