Formula Help Needed to Remove 0 From Results

Options

Hello Smartsheet Community!

I need some help to better understand what is happening. Why are only some of my results generating a leading 0 in front of my text? And how can I get rid of the "0"? I appreciate any help I can get!

Here is an example of my formula:

=IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) > 0, ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

=IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) > 0, (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @Joyce Wong

    Change the >0 in your formula to <>"". This means is not blank. the way you have it now it is searching for a numeric value.

    =IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) <> "", ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

    =IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) <> "", (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Answer ✓
    Options

    @Joyce Wong

    Change the >0 in your formula to <>"". This means is not blank. the way you have it now it is searching for a numeric value.

    =IF(([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row) <> "", ([Plant Name (China)]@row + [Plant Name (Taiwan)]@row + [Plant Name (Singapore)]@row + [Plant Name (India)]@row + [Plant Name (Thailand)]@row + [Plant Name (Japan)]@row + [Plant Name (Korea)]@row + [Plant Name (Australia)]@row), "")

    =IF((Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row) <> "", (Access@row + Catering@row + Cleaning@row + Comms@row + Drainage@row + DWS@row + Electrical@row + Fabric@row + [Fire & Sec]@row + Furnishing@row + HVAC@row + ICT@row + Leisure@row + [Medical ]@row + Plumbing@row + Safety@row + Rail@row + Transport@row + Technical@row + Utility@row + Site@row + Tools@row + [Fuel Distribution]@row), "")

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Joyce Wong
    Options

    Thank you so much! It works now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!