SUMIFS total for data within last 30 workdays

Hello,

I've read multiple questions regarding getting a return of SUMIFS for a set date range. I however can unfortunately not seem to get my function to work.

I am attempting to get a sum on the total number of orders done within a specific hour over the last 30 days. I have a form that feeds in data that tracks orders done by employees. I have time values built out to track what orders are done from 7:00-8:00, 8:00-9:00, 9:00-10:00, etc. I have a working SUMIFS that pulls totals per hour with a criterion of TODAY. What I would like to do is utilize this same approach but with the criterion of the last 30 days.

I have this function that pushes back #INCORRECT ARGUEMENT:

=SUMIFS([Total Number of Lines Picking]:[Total Number of Lines Picking], [Hour Value]:[Hour Value], "2", Date:Date, >=WORKDAY(TODAY(-1)), <=WORKDAY(TODAY(-31)))

Any help on where I am going wrong would be sincerely appreciated!

Tags:

Best Answer

  • zbr
    zbr ✭✭✭✭
    Answer ✓

    Just wanted to update with a solution that I found:

    SUMIFS([Total Number of Lines Picking]:[Total Number of Lines Picking], [Hour Value]:[Hour Value], "2", Date:Date, WORKDAY(TODAY(), -30))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @zbr

    Problem #1: After [Hour Value]:[Hour Value], you need ="2" but you just have "2"

    Problem #2: After Date:Date, you need to wrap both WORKDAY criteria up in an AND()

    I believe that should help to get it working but I'm not 100% sure if using WORKDAY is the correct function to use there...it possibly may work.

  • zbr
    zbr ✭✭✭✭
    Answer ✓

    Just wanted to update with a solution that I found:

    SUMIFS([Total Number of Lines Picking]:[Total Number of Lines Picking], [Hour Value]:[Hour Value], "2", Date:Date, WORKDAY(TODAY(), -30))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!