Percentage formula based on a checkbox.

Options

Any suggestions on how to make this exceptions formula more accurate? Smartsheets keeps adding 10 rows to my sheet with no data causing my formula to be off.

I have 88 rows with data and a total of 98 rows. Out of the 88 row 21 are an exception with the checkbox selected.

This is the formula I am using.

=COUNTIF({Master Sheet Exceptions}, 1) / COUNT({Master Sheet Exceptions})

The answer should be 23% (21*100)/88=23.86 but Smartsheet is giving me a 21% (21/100)/98=21.43.


Tags:

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/27/23
    Options

    That's annoying. Is there another column you could reference that's always populated, so you can change the "COUNTIF" to a "COUNTIFS" and use that other column as another criteria?

    I.e. = COUNTIFS({Master Sheet Exceptions}, {Master Sheet Exceptions}, 1, {Non-blank reference column}, <>"")/ COUNTIFS({Master Sheet Exceptions},{Non-blank reference column}, <>"")

    If you have a Row ID column, I don't think the row ID would populate unless you added other data to the row--you could potentially use that as your non-blank reference.

  • AlexisJ
    AlexisJ ✭✭✭
    Options

    @Lucas Rayala That is a great idea. I will tinker with the other rows to see what other criteria I can build into it.

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!