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)) )
One more solution. It uses filter function.
Also it will expose you the importance of using formula in a dynamic way.