Counting summary data by week and month

K Munz

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]))

Shaine Greenwood

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])

Noemi Kecskes

Hi

 

This formula helped me so much, thank you for posting it!

Noemi