Counting instances in each of the last 6 months on a rolling basis

Raiana Grieme

Hello,

I'm trying to count the number of times a type of dispute occurs within a month in the last 6 months in order to monitor trends. I want the months to roll so I don't need to adjust the formulas ever. My formula doesn't take the previous year into account and I'm not sure how to introduce it. This is what my background sheet looks like currently - gets stuck on 5 months ago, or Dec. 2020.

This is my formula right now that's pulling from the source sheet:

=COUNTIFS({B-Stock Dispute Tracker Range 4}, "short - entire order", {B-Stock Dispute Tracker Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()) - 4), IFERROR(YEAR(@cell), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))))

B-Stock Dispute Tracker Range 4 is my Dispute Type column. B-Stock Dispute Tracker Range 3 is my Dispute Entry Date column.

Any help would be appreciated!

Rai

Paul Newcome

For 6 months ago:

=COUNTIFS({B-Stock Dispute Tracker Range 4}, "short - entire order", {B-Stock Dispute Tracker Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + IF(MONTH(TODAY()) <= 6, 6, -6), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= 6, 1)))

Then for each of the other past # of months, you are going to want to adjust the numbers in the IF statement for the month so that they always equal 12.

IF(MONTH(TODAY()) <= 5, 7, -5)

IF(MONTH(TODAY()) <= 4, 8, -4)

IF(MONTH(TODAY()) <= 3, 9, -3)

IF(MONTH(TODAY()) <= 2, 10, -2)

IF(MONTH(TODAY()) <= 1, 11, -1)

Raiana Grieme

Thank you Paul! I ended up changing the 6 in this spot according to month as well IF(MONTH(TODAY()) <= 6, 1))) and that seems to be working with what you outlined above.

My Dispute Type column is actually a multi-select dropdown and aren't being counted. How might I add that in? I think I'd rather count a dispute in multiple buckets than not have that row counted at all.

Paul Newcome

Assuming your dispute type is the first range, you would want to try this...

=COUNTIFS({B-Stock Dispute Tracker Range 4}, HAS(@cell, "short - entire order"), {B-Stock Dispute Tracker Range 3}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) + IF(MONTH(TODAY()) <= 6, 6, -6), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= 6, 1)))