How to use COUNTIFS by Week?
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.
Best Answer
-
Give this a try:
=COUNTIFS({Official Opening Actual}, IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row)
Answers
-
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.
-
@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".
-
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.
-
Give this a try:
=COUNTIFS({Official Opening Actual}, IFERROR(WEEKNUMBER(@cell), 0) = [Week #]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!