Alternate way of pulling Countifs with WEEKNUMBER functions
Hi,
I have a weekly report that needs to count all the leads per lead source and my source sheet is pulling the data from google sheets. To count the report automatically from the sheet source, I am using this formula below. [Select week] column dictates the week number that I need. As you I have heard, WEEKNUMBER pulls Monday to Friday, but this morning my manager asked me to align this formula to our cut-off which is Saturday to Friday to tally on our reporting.
=COUNTIFS({CD1}, IFERROR(WEEKNUMBER(@cell), 0) = [Select Week]$1, {LS1}, [Lead Source]$5)
Is there a way to make the pulling of WEEKNUMBER function to Saturday to Friday?Any formula or helping columns needed? by the way source sheet is untouchable.
Answers
-
If you want to change the way the function WEEKNUMBER operates, you don't have much choice but go with an helper column to adapt it. Where you will have a formula like this:
=IF(OR(WEEKDAY(TODAY())=7, WEEKDAY(TODAY())=1), WEEKNUMBER(TODAY())+1, WEEKNUMBER(TODAY()))
WEEKDAY returns 1 for sunday up to 7 which is saturday.
You can also encapsulate this formula in your IFERROR function and replace your IFERROR criteria with this cell/range in your COUNTIFS.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 216 Industry Talk
- 456 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!