Counting previous months data

Paul Newcome

It looks like you may have simply misplaced the new range/criteria set within all those parenthesis.

 

The easiest way to make sure you are adding range/criteria sets to a COUNTIFS (especially when you have a lot of )))))))))) at the end of it), is to set your courser at the very end of the formula, and hit backspace one time. The formula help box should pop up and it should show that you are in the COUNTIFS function. Then just enter a comma, then your range, then your criteria, then it should be a single closing parenthesis to finish it off.

dave.mcpherson56751

Hi Paul,

I think i have sorted it with your advise, as always greatly appreciated.

Have a lovely evening 

 

Dave Mac

Paul Newcome

Happy to help! 

 

Feel free to reach out with any other questions or if something in the above thread stops working.

Niall D

@Paul Newcome I am building a dashboard that looks back at last month, 2 months ago and 3 months ago data.

I have managed to get the formula you shared working on my calculation sheet, however I am struggling to expand the year formula to cover for the 2 and 3 month look back.

Can you advise on how I could expand this?

Thanks

Niall

edit: Actually, the 2 month and 3 month lookback also don't seem to work, although the formula does not give an error, when I test Month 2 and 3 by replacing the month with 9, 9 to return this months data I get zero.

Here is my query:

=COUNTIFS({Range 1}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 10, MONTH(TODAY()) - 3), AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 2, 11, MONTH(TODAY()) - 3), OR(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 3, 12, MONTH(TODAY()) - 3), AND(IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY(), AND(IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 2, YEAR(TODAY()) - 1, YEAR(TODAY()))))))))), {Range 2}, [Column10]5)

Paul Newcome

@Niall D I would actually suggest using dates instead of trying the MONTH and YEAR functions like you have.

First of Last Month:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 1, 1))

End of Last Month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1

So counting for last month:

=COUNTIFS({Date Column}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 1, 1)), @cell <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

First of Two Months Ago:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 10, 1))

End of Two Months Ago:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 1, 1)) - 1

So counting for two months ago:

=COUNTIFS({Date Column}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 10, 1)), @cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) - 1, 1, 1)) - 1))

First of Three Months Ago:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1))

End of Three Months Ago:

=IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 10, 1)) - 1

So counting for three months ago:

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

Niall D

Thank you Paul,

Really appreciate the support.

Your other contributions have also helped me with other queries so thank you for all that you do.

Paul Newcome

@Niall D Happy to help. 👍️