Return Sundays Numbers as Friday

Kevin Shirey

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!

L_123

@Mike Wilday It's supposed to. I'm returning different criteria for the range using a conditional statement. I did forget the {date} range, which is probably why it didn't work...

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

1. Sumifs with return range and first criteria range
2. conditional for first criteria range. Condition is if today is monday
3. if today is monday, then return cells on range {Date} where either the day is last sunday or last friday
4. if today isn't monday, then return cells on range {Date} where the day is yesterday
5. Second range and criteria

Formula:

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

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

Kevin Shirey

@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

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

=countif(A:A,@cell>20)

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.

Kevin Shirey

@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

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

@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.

L_123

@Mike Wilday It's supposed to. I'm returning different criteria for the range using a conditional statement. I did forget the {date} range, which is probably why it didn't work...

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

1. Sumifs with return range and first criteria range
2. conditional for first criteria range. Condition is if today is monday
3. if today is monday, then return cells on range {Date} where either the day is last sunday or last friday
4. if today isn't monday, then return cells on range {Date} where the day is yesterday
5. Second range and criteria

Formula:

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

Mike Wilday

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

Kevin Shirey

@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!