Counting a list with two criteria - date and value

[Jeff-Nipro]

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.

Getting the sums for either column is easy, but when I try to use both criteria it has failed. I've tried various COUNTA, COUNTIFS, SUM, DCOUNTA configurations, including with and without AND, but cannot get this quite right. 

Example:

In the table below, there are 8 dates in January and 8 dates in February.
January has 6 days with values greater than zero and February has 4 days with values greater than zero.

When the user selects a month, I need the working cell to count the number of column B cells in the data table that correspond to that month that are greater than zero. January should come back with a 6, while February should come back with a 4.

This should be simple, but I'm stuck.

2024-01-01	5
2024-01-02	6
2024-01-03	0
2024-01-10	9
2024-01-11	7
2024-01-12	8
2024-01-13	0
2024-01-14	4
2024-02-02	11
2024-02-03	0
2024-02-04	0
2024-02-05	6
2024-02-20	3
2024-02-21	0
2024-02-22	0
2024-02-23	15

[#2 · Accepted solution]

@Jeff-Nipro 

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))
)

[#3]

With a little tweaking, this worked great. Thank you.

[#4]

You're welcome!

[#5]

@Jeff-Nipro 

One more solution. It uses filter function.

Also it will expose you the importance of using formula in a dynamic way.

[#6]

@Patrick2788 

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)
)

[#7]

@Jeff-Nipro 

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.