How can I "count" with Business/Holidays if there are blank cells?

We are creating an Out of State(OOS) Request to Smartsheet. EEs can submit up to 3 requests at one time to work OOS. Some people only had 1 requests - meaning the other 2 requests are 0. How do I create a formula that calculates the days (Business days and excludes Holidays).

the 1st request (green) has 3 dates submitted - so number under "Count 1", Count 2, Count 3 work.

The 2nd request (yellow) submitted 1 request - so Count 2 & Count 3 are blank. But I still need it add total count.

How can I do that please?

Answers

  • Jgorsich
    Jgorsich ✭✭✭✭

    I'd reformat your intake sheet/form for this - have each request get its own row, but limit them to having 3 open requests at a time; then you are avoiding the issue with empty columns. Otherwise, if you REALLY want to keep it spread across columns like this, just use some if statements to void out Count2 and Count3 as a function of your "Requests" column…so, wrap the function in Count2 like this: =if(requests@row>=2,(your current function here),0)

  • Ali N.
    Ali N. ✭✭

    Thank you for your assistance. I would like to keep the option to have 3 requests. I tried adding your formula, but doesn't seem to work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!