Formula issue showing 0-5 Stars

PeterR

Can anyone identify the error in this formula - it should show "Three" stars in the column as it's counting 3 true checkbox cells. However it will only return the "Empty" stars result.

Obviously the number of stars formula result is based on how many boxes are checked.

Andrée Starå

@PeterR

Try this.

=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 5, "Five", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 0, "Empty"))))))

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PeterR

=IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 0, "Empty", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 1, "One", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 2, "Two", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 3, "Three", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 4, "Four", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) > 5, "Five"))))))

Ipshita

Hi @PeterR the first error I can notice in your formula is in the first line, the greater than 0 because it will always return "Empty" as everything is greater than 0, instead try using the = instead of greater than only.

Hope this helps,

Cheers,

Ipshita

Andrée Starå

Hi @PeterR

I hope you're well and safe!

It should work if you reverse the order.

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

PeterR

=IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 0, "Empty", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 1, "One", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 2, "Two", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 3, "Three", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 4, "Four", IF(COUNT([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row) = 5, "Five"))))))

I've tried this but it always returns 5 stars, regardless of how many boxes are checked

Genevieve P.

Hi @PeterR

COUNT will simply count how many cells have a box that can be checked, regardless of if the box is checked or not. That means you'll always have 5, as there are 5 cells containing a box.

Instead, you'll need to use COUNTIF to see if the boxes = 1, or are checked:

=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 0, "Empty", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 5, "Five"))))))

Cheers!

Genevieve

PeterR

Sussed, thank you

Andrée Starå

@PeterR

Try this.

=IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 5, "Five", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 4, "Four", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 3, "Three", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 2, "Two", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 1, "One", IF(COUNTIF([Used More Than 3 Times Satisfactorily?]@row:[Insurance Documents Approved]@row, 1) = 0, "Empty"))))))

Did it work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!