Counting summary data by week and month
I'm collecting data on number of inspections conducted each day, and would like to calculate summaries for each week and month. I've figured out the month formula, but struggling with how to do this for each week. I think I need to create a formula that specifies the date range for each week (though if this could be more automated that would be great). Here's what I've come up with and its returning a value of 0, which is not correct. Any input is appreciated. Thank you.
Formula to calculate number of inspections conducted for first month of January:
=SUMIF(Date:Date, AND(@cell < DATE(2018, 1, 8), @cell > DATE(2018, 1, 1), [Number of Inspections]:[Number of Inspections]))
Comments
-
Hello,
The automation piece mainly depends on whether you just need to display number for the current week, or if you need to display numbers for all past weeks as well.
You might consider replacing DATE with the WEEKNUMBER function: https://help.smartsheet.com/function/weeknumber
=SUMIF(Date:Date, WEEKNUMBER(@cell) = 1, [Number of Inspections]:[Number of Inspections]))
You'd need to add this function to all rows as needed, and you'd likely need to consider using something more like this instead if your sheet has projects that span multiple years:
=SUMIFS([Number of Inspections]:[Number of Inspections], Date:Date, WEEKNUMBER(@cell) = 1, Date:Date, YEAR(@cell) = 2018)
If you're wanting to SUM the current week, you might consider something like this:
=SUMIF(Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()), [Number of Inspections]:[Number of Inspections])
-
Hi
This formula helped me so much, thank you for posting it!
Noemi
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!