Return Sundays Numbers as Friday

Pulling production numbers and am currently using the following formula to pull numbers from the previous day

=SUMIFS({GF}, {Date}, TODAY(-1), {MN}, "344")

GF = Good Footage being calculated

Date = Date Submitted

MN = Machine Number

This formula works for Tuesday - Friday but come Monday morning I need to have the data from Sunday night & Friday together.

Trying to put something together as a helper column that states if the date submitted is not a work day(Sunday), use the last work day (Friday). If it is a work day, use the same day.

Any help would be much appreciated!

Best Answer


  • L_123
    L_123 ✭✭✭✭✭✭

    =SUMIFS({GF}, IF(WEEKDAY(TODAY()) = 2, OR(@cell = TODAY(-1), @cell = TODAY(-3)), @cell = TODAY(-1)),{MN},"344")

    I think i'm the only one on here that would suggest to do use the formula in this way, most people would use a helper column to add this functionality. Take that as you will. Paul or Mike might have a different take on this problem.

    @Paul Newcome

    @Mike Wilday

  • @L@123

    for the "@cell" I am i calling to certain cells or am i inputting that formula directly because it does not work when I input it directly. Where would i be calling to the {Date} column in that formula? Sorry, I am trying to learn this all on the fly and that may be a dumb question.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/18/20

    Short Explanation:@cell references the current value in the range that is being used for criteria.


    Longer Explanation: Smartsheet Formula work like reading in english. Top to bottom left to right. The formulas check the first value against the criteria, if it matches it returns the value, if not it skips, then the formula goes to the next value, and repeats until it has cycled through the full range. @cell is a way of referencing the active value in the range that is being calculated on.

    This might not be the best formula to learn on, as I am returning criteria for the sumif range via a conditional statement, which is something that is very rarely done in smartsheet, so if you are a beginner I wouldn't use this as an example how to normally operate in the program.

  • @L@123 Here's another thought I had instead of trying to complicate things with formulas to determine if a day is a work day or not, how can I just simply reference the last work day?

    The thought is having one formula that instead of saying

    =SUMIFS({GF}, {Date}, TODAY(-1), {MN}, "344")

    it would say

    =SUMIFS({GF}, {Date}, WORKDAY(-1), {MN}, "344")

    I know this does not work because I get an "#incorrect argument set" when i do that. I just want Mondays report to pull from the last work day, which is Friday. Then on Tuesday its the same formula still doing the same thing, which is referencing the last work day, which would be Monday.

  • L_123
    L_123 ✭✭✭✭✭✭

    Can you show a snip of your sheet so I can understand the format a little better? There might be better ways to do this depending on how your sheet is organized

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    @L@123 That is too much magic for my brain! 🤯

    It looks like it might work. I have already referenced the @cell several times today for similar approaches but not for messing with dates! I can't even begin to tackle that logic.

    Although it looks like you have -1 twice in that statement.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 11/18/20

    Whoops, I missed that closing OR statement parenthesis. It looked like it was a duplicate in the OR statement.

  • @L@123 @Mike Wilday

    The formula works but I will need to verify on Monday morning that it works over the weekend. I dont have the level of access to the account in order to change my workdays in order to test the formula today. I will report back on Monday! Thank you for the help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!