Calculating Current Week Sum

kira11

Hello,

I may be overthinking this formula, but I have a grid with released work hours and the date they were released. I am trying to sum the hours released in the current week.

I have been trying with a sheet summary metric with the formula:

=SUMIF([Est Weld Hrs]:[Est Weld Hrs], WEEKNUMBER([Release Date]@row) =WEEKNUMBER(TODAY())).I have tried a couple variations of this but get different errors. If anyone can correct the formula or give me a better way of summing the current weeks hours, I'm open to either!

Kerry St. Thomas

The issue is the calculation of the weeknumber for the release date versus today's date embedded in the formula - that syntax doesn't play nice with SUMIF. You're quite close though… the calculation happens in your "Week" column already.

=SUMIF([Week Number]:[Week Number], WEEKNUMBER(TODAY()), [Est Weld Hours]:[Est Weld Hours])

Alternately, you can use COLLECT - the effective equivalent is here. You might want to explore this format if you intend to strip the helper columns, but I didn't go down that rabbit hole at all.

=SUM(COLLECT([Est Weld Hours]:[Est Weld Hours], [Week Number]:[Week Number], WEEKNUMBER(TODAY())))

Kerry St. Thomas

The issue is the calculation of the weeknumber for the release date versus today's date embedded in the formula - that syntax doesn't play nice with SUMIF. You're quite close though… the calculation happens in your "Week" column already.

=SUMIF([Week Number]:[Week Number], WEEKNUMBER(TODAY()), [Est Weld Hours]:[Est Weld Hours])

Alternately, you can use COLLECT - the effective equivalent is here. You might want to explore this format if you intend to strip the helper columns, but I didn't go down that rabbit hole at all.

=SUM(COLLECT([Est Weld Hours]:[Est Weld Hours], [Week Number]:[Week Number], WEEKNUMBER(TODAY())))

kira11

Thank you for your suggestion, @Kerry St. Thomas! I was able to get =SUM(COLLECT([Est Weld Hrs]:[Est Weld Hrs], Week:Week, WEEKNUMBER(TODAY()))) and =SUMIFS([Est Weld Hrs]:[Est Weld Hrs], [Release Date]:[Release Date], WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))to work in the sheet summary