Subtraction Issue when there is no input


I have two separate formulae to collect data based off of dates and input, I need a further formula to subtract one from the other, but I am struggling to get it to work. Subtracting them works fine, Cell v Cell, until there is no data input. I thought perhaps ISNUMBER might be a strong way to go but hit an #Unparseable.

Here is what I am working with.

Formula 1 :

=IF((SUM(COLLECT({Scrap}, {Date}, [Last Entry Date]1))) <> 0, SUM(COLLECT({Scrap}, {Date}, [Last Entry Date]1)), "")

Formula 2:

=IF(SUMIFS({HPOT}, {Process}, [Column4]@row, {Date}, MAX({Date})) <> 0, SUMIFS({HPOT}, {Process}, [Column4]@row, {Date}, MAX({Date})))

Both of these function well and return the values desired, what is my best way of subtraction formula 2's data from formula 1's, and keeping it from giving an error when it doesn't have data input?

I expect I have overthought this or am overlooking something small, I appreciate your help and time.



  • Purnima Gore
    Purnima Gore ✭✭✭✭✭✭

    Any chance you can provide a screenshot with the results you are expecting? That would make it easier to work on.

    Purnima Gore

    Cierr Limited

    Your Time is Important, you want to Stay on Track, We can help you use the Right Tools

  • Tlabeau
    Tlabeau ✭✭✭

    It was precisely what I feared, I was overthinking it and overlooking something easy. I ended up getting it to work with this:

    =IFERROR(VALUE([Scrap]6) - VALUE([12/16 Update 2]1), "")

    Were the solution a snake, I would have been bitten. Thank you very much for your thoughts though and your time, this community is absolutely wonderful. I have silently learned from reading the many questions and answers that can be found here.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!