• Status: Solved
• Priority: Medium
• Security: Public
• Views: 61

# Excel - If every cell in row has "Done" then give a 100%

Hi experts.
Looking for a conditional formatting that looks for the word "Done" in every cell in a range in a row.
If all the cells have the word "Done" then we can put a 100% text in the desired cell at the end of the row.

Thanks Experts.
Chris
0
chris pike
• 5
• 2
1 Solution

Volunteer Chief Information OfficerCommented:
in the place where you want the percentage do this:

1. format the cell as percent for visual sake.
2. you want to countif all blocks that say done, then divide that into all the blocks with values:

=COUNTIF(<<PUT RANGE IN HERE>>,"Done")/COUNTA(<<PUTRANGE IN HERE>>)
0

Excel VBA DeveloperCommented:
This isn't handled with Conditional Formatting, but can be evaluated in a couple of different methods:

1) Compare the number of cells with "Done" against the number of rows in the range.
=IF(COUNTIF(\$B\$2:\$B\$10,"Done")=ROWS(\$B\$2:\$B\$10),"100%","")

2) Using an array function ([Ctrl]+[Shift]+[Enter] to enter the formula), check if all cells in range = "Done"
=IF(AND(\$B\$2:\$B\$10="Done"),"100%","")

3) Generate a percentage result by dividing number of cells with "Done" against all cells in range.
Note that the previously-suggested solution only works if every cell in the range has a value; if there are any blanks the result will be incorrect.
=COUNTIF(\$B\$2:\$B\$10,"Done")/ROWS(\$B\$2:\$B\$10)

See attached workbook for examples of all three.

-Glenn
EE-Q_28978105.xlsx
1

Author Commented:
Thanks Glen,\
I think I should have given additional info..  I need done in columns, not rows.
0

Author Commented:
Thanks Evan,
I think we are having the same problem, with rows instead of columns.
This is what I am looking for.
0

Excel VBA DeveloperCommented:
My oversight; sorry.

The solution method is the same, just need to transpose the ranges to look up AND change the quantity of ROWS to COLUMNS.  Using your example any these would work:
1) Compare the number of cells with "Done" against the number of rows in the range.
=IF(COUNTIF(\$B20:\$F20,"Done")=COLUMNS(\$B20:\$F20),"100%","")

2) Using an array function ([Ctrl]+[Shift]+[Enter] to enter the formula), check if all cells in range = "Done"
=IF(AND(\$B20:\$F20="Done"),"100%","")

3) Generate a percentage result by dividing number of cells with "Done" against all cells in range.
=COUNTIF(\$B20:\$F20,"Done")/COLUMNS(\$B20:\$F20)

I note that your new example displays a percentage regardless of whether it is equal to 100%; this differs from your intial question.  If you do indeed want the percentage value, then only the third solution is appropriate.

I've revised the example workbook with a new sheet showing each of these possible solutions.

Regards,
-Glenn
EE-Q_28978105.xlsx
0

Author Commented:
Thanks Glen
I love your style of helping. (offering three different styles) Awesome.

However, I could not get it working.
If it is just TEXT then it works, but in my case, there is a formula in the DONE cell already that is putting DONE into that cell.

How do we make it work if there is a formula already in there???
Thanks so much/
Chris
0

Author Commented:
Thanks Evan, needed a little more help on this one.
0

Author Commented:
Thanks Glenn, works great if only TEXT is in cell. As it was what I
I will ask a second question to make it work with cells that already have a formula in them.
Thanks so much.
Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.