Month over Month Data

Monica Gallegos

I used the suggested formula because i'd like to show month over month subscribers dynamically.

Suggested formula : =COUNTIFS({Source Sheet Order Date Column}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

My formula: =COUNTIFS({ERG MEMBERSHIP SHEET Date Created}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

I would like to change the date portion to count the previous months data TODAY()) - 1 (this worked) if I wanted to go back the even one more month would that be TODAY()) -2 ?

.

Paul Newcome

That is technically correct for the most part. However...

If you are in January and trying to pull December, this will throw an error because 1 - 1 = 0 and there is no month zero.

To account for this, we need to use another IFERROR.

AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))

The above would be the portion that causes an error. So if we are in January and want to pull December, we would want to specify Month = 12 and Year = YEAR(TODAY()) - 1

AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)

Next we use the IFERROR to tie these two together:

IFERROR(AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1))

Dropping that into the criteria section of your formula should have you up and running for the previous month.

=COUNTIFS({ERG MEMBERSHIP SHEET Date Created}, IFERROR(AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())), AND(IFERROR(MONTH(@cell), 0) = 12, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1)))

Going back 2 months is definitely going to be a little different, so let's make sure the "previous month" formula is working for you before we try to tackle the next challenge.

Monica Gallegos

This was helpful thank you!

Paul Newcome

Happy to help! Did you still want some assistance with -2 months?