Sum of submissions last week

Hi there,

I want to create a formula that will return the sum of submissions in a column from the previous week (I want a static snapshot from the previous week, as opposed to a rolling 7-days).

I have a formula that is working for returning the sum of submissions from the previous month, and I tried modifying it to pull last week but I failed at that - the formula below is only pulling a single day and I need Monday-Friday.

=SUMIFS([FIMC Invoices Received]:[FIMC Invoices Received], Date:Date, WEEKDAY(@cell) = WEEKDAY(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))


Thank you!

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Lindsay Scarey

    The function WEEKDAY lists days of the week. The function you need is WEEKNUMBER

    =SUMIFS([FIMC Invoices Received]:[FIMC Invoices Received], Date:Date, WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()) - 1, Date:Date, YEAR(@cell) = YEAR(TODAY()))

    The WeekNumber function begins on Monday through Sunday.

    Will this work for you?

    Kelly

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!