Dealing with Date Ranges and Countifs function

Christopher.Rose · 09/12/24

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 · 09/12/24

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 · 09/13/24

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 · 09/12/24

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

Christopher.Rose · 09/12/24

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

Paul Newcome · 09/12/24