Forum Discussion
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.
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 |
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)) )- With a little tweaking, this worked great. Thank you.
- You're welcome!
One more solution. It uses filter function.
Also it will expose you the importance of using formula in a dynamic way.
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.
