Help with Calculation

Options
Carlos Yanes
Carlos Yanes ✭✭✭✭✭

Hey all,


I have a form with multiple fields. I'm trying to figure out how I can create some type of count that would tell me, how many fields in that form were filled out vs. not filled out. In addition, I'd need to apply several filters to the data that is being counted.


Is this even possible? 🀯😬


Thanks,

Carlos

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“
    Options

    You are going to have to use a series of IF statements outputting a one and adding them together to get the count.


    =IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + ....................................


    Once you have your count, you would want to compare it to the total number required

    =IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing")


    Then if you only wanted that to happen based on criteria in another column:

    =IF([Another Column]@row = "criteria", IF(IF([1st Required]@row <> "", 1) + IF([2nd Required]@row <> "", 1) + IF([3rd Required]@row <> "", 1) + .................................... = 11, "output if all are filled out", "output if any are missing"))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!