Help with countif date more than two, three months ago and older where certain status is not true

Julia Bailey

Hi I'm trying to report on the number of cases which were opened: last month, the month before , two months ago and all others before that BUT where the status is NOT 'closed'. This needs to take into account those opened in the previous years (some cases go back to 2017)

I have used the following formula for last month (without the status bit) but can't figure out the other date ranges and how to exclude the closed cases

=COUNTIFS({PSL Dev Request MASTER Range 4}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Paul Newcome

Last Month...

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Two Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 11, IF(MONTH(TODAY()) = 2, 12, MONTH(TODAY()) - 2)), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) <= 2, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Three Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 10, IF(MONTH(TODAY()) = 2, 11, IF(MONTH(TODAY()) = 3, 12, MONTH(TODAY()) - 3))), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) <= 3, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Everything Prior To Three Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, @cell<= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) <= 3, 1, 0), IF(MONTH(TODAY()) = 1, 10, IF(MONTH(TODAY()) = 2, 11, IF(MONTH(TODAY()) = 3, 12, MONTH(TODAY()) - 3))), 1) - 1)

Charles Young

You might need to layout the months in a column and then have the result formulated in the column next to it. I did the following and seems to get you to the point you might be looking for.

I'm also referencing the Month and Year columns in the formula to easily copy down.

Total Cases: =COUNTIF(Date1:Date22, AND(IFERROR(YEAR(@cell), 0) = Year@row, IFERROR(MONTH(@cell), 0) = Month@row))

Cases Not Closed: =COUNTIFS($Date$1:$Date$22, AND(IFERROR(YEAR(@cell), 0) = Year@row, IFERROR(MONTH(@cell), 0) = Month@row), $Status$1:$Status$22, <>"Closed")

Julia Bailey

I'm sorry, probably be me being thick - I don't have a good understanding of how the formulas work I generally find something that is doing what I want and copy it!

I don't get what the Month and Year columns are doing? - where are they referenced in the formulas and where should they sit on the sheet? They seem to be just random rows in the middle of the rest of the information?

I am using a summary sheet to collate the information from the Master sheet and I refer to the summary sheet in my report rather than the master - not sure if that makes a difference.

Also how does this help me pull out the breakdown groupings I need: last month, month before last, two months before last and a total of all prior to this?

Paul Newcome

Last Month...

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Two Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 11, IF(MONTH(TODAY()) = 2, 12, MONTH(TODAY()) - 2)), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) <= 2, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Three Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 10, IF(MONTH(TODAY()) = 2, 11, IF(MONTH(TODAY()) = 3, 12, MONTH(TODAY()) - 3))), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) <= 3, YEAR(TODAY()) - 1, YEAR(TODAY()))))

Everything Prior To Three Months Ago:

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, @cell<= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) <= 3, 1, 0), IF(MONTH(TODAY()) = 1, 10, IF(MONTH(TODAY()) = 2, 11, IF(MONTH(TODAY()) = 3, 12, MONTH(TODAY()) - 3))), 1) - 1)

Julia Bailey

@Paul Newcome that worked perfectly, thank you so much. Can I pick your brains a little more and ask how I can work out (again for the status NOT closed) those cases aised more than 1 month but less than 1 year and those greater than a year ago?

Paul Newcome

More than 1 month but less than 1 year would have the date criteria looking something like this...

AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY())), DATE(YEAR(TODAY()) - 1, 12, DAY(TODAY()))), @cell >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY())))

Which gives us...

=COUNTIFS({Status}, @cell <> "Closed", {Date Raised}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, DAY(TODAY())), DATE(YEAR(TODAY()) - 1, 12, DAY(TODAY()))), @cell >= DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))))