Better Understanding of formula

JP Pedicino

I have the following formula in one of my sheets that I modified from one of the templates. It works just fine but after review the IFERROR information online I am still confused.

At this point I don't really understand how the "52" plays a role in the formula other than knowing there are 52 weeks in a year. Can someone provide me a narrative of how this formula works?

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = 52, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1), COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Genevieve P.

Hi @JP Pedicino

Let me try to write it in english for you! So this formula takes two scenarios into account:

IF today is the first week of the year or not.

Let's start with the second half, all the other weeks of the year:

COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()) - 1, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()

This says:

Count the number of rows in the {Created Date Range} where the week number is the same as Last Week's week number, and it's the same year as Today's year.

But think about what happens if today's week number is 1... if you take 1 - 1 it would equal a week of 0, which isn't correct. We want to look back into "last week" which is actually week 52 of the previous year.

So that's what the first half of the formula does!

=IF(WEEKNUMBER(TODAY()) = 1, COUNTIFS({Created Date Range}, IFERROR(WEEKNUMBER(@cell), 0) = 52, {Created Date Range}, IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - 1),

If the Week Number of Today is 1, then search for week 52 and Today's Year - 1 (last year).

All the IFERRORS can be confusing, but they're essentially there to make sure the formula doesn't error when it comes across blank cells. We wrap it around the WEEKNUMBER function and the YEAR function to say that if there's a blank cell, treat it like 0, not an error.

If the formula is hard to read, you can take them out to make sense of it (but put them back in, in your sheet):

=IF(WEEKNUMBER(TODAY()) = 1,

COUNTIFS({Created Date Range}, WEEKNUMBER(@cell) = 52, {Created Date Range}, YEAR(@cell) = YEAR(TODAY()) - 1),

otherwise:

COUNTIFS({Created Date Range}, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, {Created Date Range}, YEAR(@cell) = YEAR(TODAY())))

Does that help?

Cheers,

Genevieve