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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!