Need a formula for counting information based on date
Hello,
I need a formula that can help me calculate the sum within a column based on date. Not sure if this can be achieved. We are updating information on a weekly basis and I have different rows for the same date. I need a formula to give me the sum based on the date. For example, on 1/17 I will have 6 rows with data that shows hours of worked for each team. This week on Friday we will have new data, so I want the formula to be able to tell me what's my sum for the whole week. If this makes sense, like below, on the Sum column I want the total hours for week ending 1/17 and 1/24 and so forth. I am open to using anything, if anyone has any other suggestions on representing this, please let me know.
Thanks,
Best Answer
-
Here is another rather flexible option...
=SUMIFS([Total Hours]:[Total Hours], [Week Ending]:[Week Ending], [Week Ending]@row)
This will sum all of the numbers in the [Total Hours] column that have the same date in the [Week Ending] column as the date in whatever row the formula is on.
Answers
-
=SUMIF([week ending]:[week ending], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Total Hours]:[Total Hours])
The formula above should show the sum of week. You can change the "today()" part of the formula to whatever you like to get different results, as I don't think I fully understand how you are trying to format your sheet.
-
Hi L@123,
thanks for sending over the formula, however that changes as the days move along, like Today's date would change tomorrow and I wouldn't be able to tell what was the sum for week of 1/17.
Basically I want to know how many total hours I had on week ending 1/17 and the week after and so forth. I still want that visibility for previous weeks.
-
@Jona Gjylameti try this formula, it is very specific to the day.
=SUMIFS({Total hours},{Week ending}, >=DATE(20, 1, 17), {Week ending}, <=DATE(20, 1, 24))
hope this helps.
-
Here is another rather flexible option...
=SUMIFS([Total Hours]:[Total Hours], [Week Ending]:[Week Ending], [Week Ending]@row)
This will sum all of the numbers in the [Total Hours] column that have the same date in the [Week Ending] column as the date in whatever row the formula is on.
-
@Diana thank you!
@Paul Necome this is great, thanks a lot!
-
Happy to help! 👍️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others with a similar challenge know that a solution is available.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!