CountIfs with Distinct and a date range

A.J.

I am sure it is doable, but I continue to struggle to get CountIFS, AND, and DISTINCT to work together. I need to count distinct dates in a long column and need to determine how many distinct dates there were in particular months.

I'm able to use this to see distinct counts after a certain date:

=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30))

However, I need to also limit the returning value to an end date, in that particular case, <DATE(2023, 8, 1).

How can I get a "between" value to work, here?

If there's a better way, maybe something with the MONTH function, please let me know!

AravindGP

Hi @A.J.

You can use this formula

=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30), DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), <DATE(2023,8,1))

AravindGP

Hi @A.J.

You can use this formula

=COUNTIFS(DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), >DATE(2023, 6, 30), DISTINCT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date]), <DATE(2023,8,1))

Paul Newcome

You actually need a COUNT/DISTINCT/COLLECT combo.

=COUNT(DISTINCT(COLLECT([CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date], [CALC_Row Actual Dissemination Date]:[CALC_Row Actual Dissemination Date], AND(IFERROR(MONTH(@cell), 0) = 7, IFERROR(YEAR(@cell), 0) = 2023))))

A.J.

hi @Paul Newcome. I used the formula that was provided at the top and it worked. Can you tell me what the outcome difference would be for the one you suggested?

Thank you, as always, for your terrific help!

Paul Newcome

Shouldn't be a difference in outcome. It is more about efficiency, readability, and management.

Mine says we are going to COLLECT everything from range 1 where range 2 (in this case the same as range 1) meets the criteria. Then COUNT the DISTINCT entries.

I also prefer to run month/year calculations without using specific dates as (for me at least) it is easier to just plug in the month and year number that I am looking for.