Not sure how to write formula correctly, not even sure this is a SUMIFS

Tyler12345

Hello,

Apologies for yet another basic question, but I'm having troubles wrapping my head around how to get a formula to behave a certain way. I'm still working on a PTO manager tool (requests PTO, automated approvals, and auto calculations on PTO Balance, etc...).

I have 3 total grids (Submission grid, Track grid, and Calculation/Helper grid). The submission grid intakes the data from the form. That data is moved to the track grid, which helps with the VLOOKUP and stores the PTO being requested off. The Calculation/Helper grid does most all the calculations. I've uploaded screenshots of each.

I'm trying to get the formula in the [PTO Taken] column rows to use the value in the [PTO Used] column row, but only if the value in [PTO Used 2] column row is blank. If it is not blank, then the formula should use the [PTO Used] column.

Calculation Grid.JPG

Track Grid.JPG

I'm over my head on this one, I was able to the formula to grab the value from [PTO Used] column row, but not to check both and use only the one I want. I also apologize if this has been covered somewhere else, please feel free to link me.

Thanks for the help,

Tyler

Paul Newcome

Let's try a SUMIFS without the VLOOKUP first... We will also strip off the IFERROR for now so that we can clearly see if there are any issues.

=SUMIFS({Calc Sheet V4 Range 3}, {Calc Sheet V4 Range 1}, Weeknumber@row)

Tyler12345

Thanks Paul, Let me finish up some work tasks and I'll jump back to this later today. Can't wait to try something new, a sanity check was greatly needed, thanks for that.

Paul Newcome

Ok. So we will add two SUMIFS together. One that will pull the Partial column where Used is a number (meaning there is a number in both columns), and another to pull the Used column where the Partial is blank (there shouldn't be a double count because the blanks in the partial will be added as zeros.

=SUMIFS({Calc Sheet V4 Partial Column}, {Calc Sheet V4 Used Column}, ISNUMBER(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

and

=SUMIFS({Calc Sheet V4 Used Column}, {Calc Sheet V4 Partial Column}, ISBLANK(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

=SUMIFS({Calc Sheet V4 Partial Column}, {Calc Sheet V4 Used Column}, ISNUMBER(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row) + SUMIFS({Calc Sheet V4 Used Column}, {Calc Sheet V4 Partial Column}, ISBLANK(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

Tyler12345

Forgot to add these formula details:

• PTO Track V4 - [PTO Taken 2] column formula =IFERROR(SUMIF({Calc Sheet V4 Range 1}, VLOOKUP(Weeknumber@row, {Calc Sheet V4 Range 4}, 1, false) = Weeknumber@row, {Calc Sheet V4 Range 3}), 0)

1.  {Calc Sheet V4 Range 1} = Calc Sheet V4 - [Weeknumber]:[Weeknumber]
2.  {Calc Sheet V4 Range 4} = Calc Sheet V4 – [Weeknumber]:[PTO Used]
3.  {Calc Sheet V4 Range 3} = Calc Sheet V4 – [PTO Used]:[PTO Used]

• Calc Sheet V4 - [PTO Balance] column formula =IFERROR(VLOOKUP(Weeknumber@row - 1, {PTO Track V4 Range 1}, 2, false), " ")

1. {PTO Track V4 Range 1} = PTO Track V4 - Weeknumber:[PTO Available]

• Calc Sheet V4 - [PTO Used (Including Partial)] column formula =IF(Checkbox@row = 1, SUM([PTO Used]@row - [Partial Total]@row))
• Calc Sheet V4 - [PTO Used] column formula =SUM([Converts Workdays Into Hours]@row + [Partial Total]@row)

All other formulas are basic/common uses. Let me know if anyone needs more details.

Thanks again

Paul Newcome

Let's try a SUMIFS without the VLOOKUP first... We will also strip off the IFERROR for now so that we can clearly see if there are any issues.

=SUMIFS({Calc Sheet V4 Range 3}, {Calc Sheet V4 Range 1}, Weeknumber@row)

Tyler12345

Thanks Paul, Let me finish up some work tasks and I'll jump back to this later today. Can't wait to try something new, a sanity check was greatly needed, thanks for that.

Tyler12345

@Paul: The formula you gave me worked, it displays a value of 21.

Paul Newcome

Excellent! Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that other searching for a similar solution can know that one may be found here.

Tyler12345

Err sorry 21 means nothing to you without examples. [PTO Taken 2] contains the original formula. [PTO Taken 3] was a second try. [Try 3] is exactly that.

Tyler12345

Hmm I think I spoke too soon. The formula does work, but doesn't behave the way I thought. Right now it is only adding up the [PTO Used] column.

I need it to sum either the [PTO Used] column or the [PTO Used (Including Partial)] column. It should use the [PTO Used (Including Partial)] column value if there is a value present. I was trying to do something like this....Use [PTO Used] column value if one of the two columns is blank. Use [PTO Used (Including Partial)] if there are values present in both columns. However I can't make that work, but would love something similar.

Paul Newcome

Ok. So we will add two SUMIFS together. One that will pull the Partial column where Used is a number (meaning there is a number in both columns), and another to pull the Used column where the Partial is blank (there shouldn't be a double count because the blanks in the partial will be added as zeros.

=SUMIFS({Calc Sheet V4 Partial Column}, {Calc Sheet V4 Used Column}, ISNUMBER(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

and

=SUMIFS({Calc Sheet V4 Used Column}, {Calc Sheet V4 Partial Column}, ISBLANK(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

=SUMIFS({Calc Sheet V4 Partial Column}, {Calc Sheet V4 Used Column}, ISNUMBER(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row) + SUMIFS({Calc Sheet V4 Used Column}, {Calc Sheet V4 Partial Column}, ISBLANK(@cell), {Calc Sheet V4 Range 1}, Weeknumber@row)

Tyler12345

Amazing Paul, that worked perfectly. Thanks a ton! Now to test out a few edge cases, but I'm about 90% sure we have covered what I (my company) needs. Thanks a ton! Always appreciate community help. The Smartsheet community are the best peoples, can't wait for Engage 2020!

Paul Newcome

Happy to help! 👍️