SUMIFS and Weekday to pull totals for report weekly

Hi all,


I am trying to figure out a way to 1). sum data coming in between Monday and Saturday) in the sheet summary, and 2). use this to create a report with totals based on our workweek (Monday-Saturday).

I created a helper column to utilize the "WEEKDAY" function ("Date Lab Results RCVD Helper"). Getting this to sum the data seemed to work fine with the following:

=SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD Helper]:[Date Lab Results RCVD Helper], @cell <= "2"

Can I use the TODAY function in addition to the above SUMIF to make sure only this week is being pulled?

([Date Lab Results RCVD]:[Date Lab Results RCVD], @cell = TODAY()-7)


I feel like I'm probably over thinking it.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Assuming that "Date Lab Results RCVD" is the name of your date column...


    =SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD]:[Date Lab Results RCVD], AND(IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

  • Megan TW
    Megan TW ✭✭✭✭

    Hey Paul, thanks for your help. I'm missing something here as my value returned is 0. :(

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Make sure that the [Quantity of Units] column contains actual numerical values and that the [Date Lab Results RCVD] column contains actual date values.


    Also look at the data and make sure that there are actually rows that meet the date criteria in the source data.

  • Megan TW
    Megan TW ✭✭✭✭

    Paul,


    This is what I did to get it to work:


    =SUMIFS([Quantity of Units]:[Quantity of Units], [Date Lab Results RCVD]:[Date Lab Results RCVD], AND((IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))))


    For whatever reason when I added in the IFERROR(YEAR(@cell), 0) = YEAR(TODAY()))) I kept getting 0. :/

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It depends on how exactly you added it. It looks like you used an extra set of parenthesis in your formula, so it could be that they were misplaced with the YEAR function built in.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!