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.