Count Referrals by Week when Week Falls Between Two Months

Courtney Coules

On my source sheet, I have three columns calculated off of a created by date column for day, month, and year. I have the first formula working correctly to count referrals received by week:

=COUNTIFS({OBOT Referrals Month}, @cell = 1, {OBOT Referrals Year}, @cell = 2023, {OBOT Referrals Day}, AND(@cell >= 1, @cell <= 7))

However, I keep getting #INCORRECT ARGUMENT when I attempt to count between months:

=COUNTIFS(OR({OBOT Referrals Month}, @cell = 1, {OBOT Referrals Day}, AND(@cell >= 29, @cell <= 31), {OBOT Referrals Year}, @cell = 2023, {OBOT Referrals Month}, @cell = 2, {OBOT Referrals Day}, AND(@cell >= 1, @cell <= 4), {OBOT Referrals Year}, @cell = 2023))

Genevieve P.

Hi @Courtney Coules

To correct your second formula, I would add 2 COUNTIFS together, one for each month:

=COUNTIFS({OBOT Referrals Month}, @cell = 1, {OBOT Referrals Day}, AND(@cell >= 29, @cell <= 31), {OBOT Referrals Year}, @cell = 2023) + COUNTIFS({OBOT Referrals Month}, @cell = 2, {OBOT Referrals Day}, AND(@cell >= 1, @cell <= 4), {OBOT Referrals Year}, @cell = 2023)

However, it looks like you may be wanting to count by week instead of by Month. Have you though about using the WEEKNUMBER function?

I do this in my sheets - I'll have a hidden Weeknumber column next to my dates, then reference that in a formula. For example:

But it looks like your Week starts on Sunday, not Monday (is that correct?). If so, you can adjust the WEEKNUMBER formula to count Sundays as the start of the week:

=IF(WEEKDAY([Date Column]@row) = 1, IFERROR(WEEKNUMBER([Date Column]@row) + 1, ""), IFERROR(WEEKNUMBER([Date Column]@row), ""))

Then in your formula you would only need to look for the WeekNumber and the Year:

=COUNTIFS({OBOT WeekNumber}, 1 {OBOT Referrals Year}, @cell = 2023)

If you want to get really fancy, you could reference today's Weeknumber in your formula for a rolling formula that doesn't need to be updated each week (as long as you're not looking at the formula on a Sunday).

This week:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER(TODAY()), {OBOT Referrals Year}, @cell = 2023)

Last week:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER(TODAY()) - 1, {OBOT Referrals Year}, @cell = 2023)

Or you could have a date cell somewhere in this sheet to input the date you're wanting to look for, so instead of changing the formula you just change a date in a cell that the formula is looking at:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER([Date Reference]@row), {OBOT Referrals Year}, @cell = 2023)

Again, make sure that date isn't a Sunday, as we haven't adjusted this Weeknumber function to see Sunday as the start of the week.

Let me know if you want to see screen capture examples of anything I mentioned above and I'd be happy to clarify further!

Cheers,

Genevieve

Genevieve P.

Hi @Courtney Coules

To correct your second formula, I would add 2 COUNTIFS together, one for each month:

=COUNTIFS({OBOT Referrals Month}, @cell = 1, {OBOT Referrals Day}, AND(@cell >= 29, @cell <= 31), {OBOT Referrals Year}, @cell = 2023) + COUNTIFS({OBOT Referrals Month}, @cell = 2, {OBOT Referrals Day}, AND(@cell >= 1, @cell <= 4), {OBOT Referrals Year}, @cell = 2023)

However, it looks like you may be wanting to count by week instead of by Month. Have you though about using the WEEKNUMBER function?

I do this in my sheets - I'll have a hidden Weeknumber column next to my dates, then reference that in a formula. For example:

But it looks like your Week starts on Sunday, not Monday (is that correct?). If so, you can adjust the WEEKNUMBER formula to count Sundays as the start of the week:

=IF(WEEKDAY([Date Column]@row) = 1, IFERROR(WEEKNUMBER([Date Column]@row) + 1, ""), IFERROR(WEEKNUMBER([Date Column]@row), ""))

Then in your formula you would only need to look for the WeekNumber and the Year:

=COUNTIFS({OBOT WeekNumber}, 1 {OBOT Referrals Year}, @cell = 2023)

If you want to get really fancy, you could reference today's Weeknumber in your formula for a rolling formula that doesn't need to be updated each week (as long as you're not looking at the formula on a Sunday).

This week:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER(TODAY()), {OBOT Referrals Year}, @cell = 2023)

Last week:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER(TODAY()) - 1, {OBOT Referrals Year}, @cell = 2023)

Or you could have a date cell somewhere in this sheet to input the date you're wanting to look for, so instead of changing the formula you just change a date in a cell that the formula is looking at:

=COUNTIFS({OBOT WeekNumber}, WEEKNUMBER([Date Reference]@row), {OBOT Referrals Year}, @cell = 2023)

Again, make sure that date isn't a Sunday, as we haven't adjusted this Weeknumber function to see Sunday as the start of the week.

Let me know if you want to see screen capture examples of anything I mentioned above and I'd be happy to clarify further!

Cheers,

Genevieve