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

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.

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

Best Answers

  • Tyler12345
    Tyler12345 ✭✭✭
    Answer ✓

    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.

Answers

  • Tyler12345
    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

  • Tyler12345
    Tyler12345 ✭✭✭
    Answer ✓

    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
    Tyler12345 ✭✭✭

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

  • Paul Newcome
    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
    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
    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.

  • Tyler12345
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!