Calculating Current Week Sum

Options

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!

Best Answer

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭
    Answer ✓
    Options

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

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

Answers

  • Kerry St. Thomas
    Kerry St. Thomas ✭✭✭✭✭
    Answer ✓
    Options

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

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • kira11
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!