How to use COUNTIFS by Week?

haley brianne

Hi-

I am trying to write a formula for how many locations have officially opened per week of this year.

This is what I currently have and all it gives me is 0.

=COUNTIFS({Official Opening Actual}, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

For the Official Opening Actual, I am referencing another sheet. I am not sure if I need the column, Week #.. I was just trying everything I have seen within the community.

I need to delete rows 1 & 2 - I just haven't gotten around to doing so.

Paul Newcome

Give this a try:

=COUNTIFS({Official Opening Actual}, IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row)

haley brianne

@Genevieve P.

Austin Smith

@haley brianne

Nah, you don't necessarily need the Week # column.

Slight modification on the formula you had to make it work. This formula will find whether the week# of a date matches the week# of today with only a single input, the date in question. Not sure how it gets used in the sample.

=IFERROR(COUNTIF([Official Opening Actual], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())),0)

For the rightmost column you have in the sample, if you want to lock in # of opens by week, you'll need 52 formulas like the one below (one for each week). Alternatively, having the reference helper column with numbers 1-52 is an option but puts you back where you are now.

=IFERROR(COUNTIF([Official Opening Actual], WEEKNUMBER(@cell) = 1),0)

=IFERROR(COUNTIF([Official Opening Actual], WEEKNUMBER(@cell) = [Week #]@row),0)

I'm pro helper column in general. Easy to hide, easy to manipulate, standardizes data, cuts chunks and errors out of formulae.

haley brianne

@Austin Smith I tried the last formula

=IFERROR(COUNTIF({Official Opening Actual}, WEEKNUMBER(@cell) = [2023 WK 1-52]@row), 0)

It does the same thing.. gives me all 0's.

The only column I am working on here is the "2023 Open Total by Week". I have a helper column which is "2023 WK 1-52".

Austin Smith

@haley brianne

Countif can't work with range and criteria being different formats and different numbers. Dates don't equal integers.

You probably already did this, but the simplest, fully functional option, while going in the opposite direction that you wanted is:

Make a helper column on the source reference sheet that is =WEEKNUMBER(date reference)

=countif([week # col from source sheet]:[week # col from source sheet], [Week #]@row)

Will try at it some more. Maybe someone will get you a better answer faster.

Paul Newcome

Give this a try:

=COUNTIFS({Official Opening Actual}, IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row)