CountIfs with Distinct and a date range

Options
A.J.
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!

Tags:

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭
    Answer ✓
    Options

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

    Thanks,

    Aravind GP| Principal Consultant

    Atturra Data & Integration

    M: +61493337445

    E:Aravind.GP@atturra.com

    W: www.atturra.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.
    A.J. ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!