Need a formula for counting something on a weekly basis.
From the screenshot below, I need a formula to count the incoming mail based on each week. I want to show the information on a dashboard on a weekly basis which will exclude the weekends. For example here, I have the data for last week of Jan, but when we enter into next week, first week of feb, I want the dashboard to show new numbers as we enter daily. Is this possible?
Best Answer
-
You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.
A SUMIF works like this:
=SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])
In your case, the criteria is as follows:
WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())
So, put together, try this:
=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])
You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.
Let me know if this works for you or if you have any additional questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
You can use the WEEKNUMBER function to find the rows that have Today's Weeknumber in the Date column. Then you can embed this criteria in a SUMIF statement.
A SUMIF works like this:
=SUMIF([Column 1]:[Column 1], "Criteria 1", [Sum Column]:[Sum Column])
In your case, the criteria is as follows:
WEEKNUMBER(@cell) = WEEKNUMBER(TODAY())
So, put together, try this:
=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Incoming Mail]:[Incoming Mail])
You can have this input into a Sheet Summary Field (see here) and then use this in a metric widget.
Let me know if this works for you or if you have any additional questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!