Percentage formula based on a checkbox.

AlexisJ

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.

Lucas Rayala

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

@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

@AlexisJ glad to help!