Dealing with Date Ranges and Countifs function

Christopher.Rose

I have a metric sheet that I am trying to pull data from another sheet.

I am trying to get counts by person that fall:

within 3 months

within 3-6 months

within 6-9 months

within 9-12 months

within 12 months.

I have done a few searches within the community, but I cannot wrap my head around this. Any thoughts?

Paul Newcome

Give these a go…

within 3 months:

=COUNTIFS({Date Range}, AND(@cell >= TODAY(), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 9, DAY(TODAY())))))

within 3-6 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 9, DAY(TODAY()))), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 6, DAY(TODAY())))))

within 6-9 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 6, DAY(TODAY()))), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 9, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 3, DAY(TODAY())))))

within 9-12 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 9, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 3, DAY(TODAY()))), @cell <= DATE(YEAR(TODAY()) + 1, MONT(TODAY()), DAY(TODAY()))

within 12 months:

=COUNTIFS({Date Range}, AND(@cell >= TODAY(), @cell <= DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))))

Paul Newcome

Happy to help. 👍️

Sorry about missing that H. It happened quite a few times when typing those out, but I seem to have missed that one. I think it may be time for a new keyboard. Haha.

Paul Newcome

Am I correct in assuming that you mean within # months of today's date?

Christopher.Rose

Paul, yeah, # of months from today's date.

Paul Newcome

Give these a go…

within 3 months:

=COUNTIFS({Date Range}, AND(@cell >= TODAY(), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 9, DAY(TODAY())))))

within 3-6 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 3, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 9, DAY(TODAY()))), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 6, DAY(TODAY())))))

within 6-9 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 6, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 6, DAY(TODAY()))), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 9, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 3, DAY(TODAY())))))

within 9-12 months:

=COUNTIFS({Date Range}, AND(@cell > IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 9, DAY(TODAY())), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 3, DAY(TODAY()))), @cell <= DATE(YEAR(TODAY()) + 1, MONT(TODAY()), DAY(TODAY()))

within 12 months:

=COUNTIFS({Date Range}, AND(@cell >= TODAY(), @cell <= DATE(YEAR(TODAY()) + 1, MONTH(TODAY()), DAY(TODAY()))))

Christopher.Rose

THANK YOU!!! THANK YOU!!! THANK YOU!!!

These worked like a charm. Note to anyone else who uses this - the 9-12 section is missing an "H" in Month!

Paul Newcome

Happy to help. 👍️

Sorry about missing that H. It happened quite a few times when typing those out, but I seem to have missed that one. I think it may be time for a new keyboard. Haha.