Excel Cell already has formula and can not apply a second formula.

I have three great formulas the looks across columns for DONE in cells and then gives a percentage in the last column.
The problem is, all the cells already have a formula in them that is putting the DONE in the cell.

Here are the formulas. (all work great if it is just TEXT, but not if a formula is already in the cell)
=IF(COUNTIF(\$B20:\$F20,"Done")=COLUMNS(\$B20:\$F20),"100%","")

=IF(AND(\$B20:\$F20="Done"),"100%","")

=COUNTIF(\$B20:\$F20,"Done")/COLUMNS(\$B20:\$F20)

Thanks
Chris
Who is Participating?

Group Finance ManagerCommented:
I'm not sure if I understand the problem correctly. When I put =COUNTIF(\$C6:\$AN6,"Done")/COLUMNS(\$C6:\$AN6) into A)6 it returns 95. Done in that row is generated by formulas
Dummy-Done-100-.xlsx
0

Group Finance ManagerCommented:
Have you tried using the TEXT function  with the other formulas?
0

Author Commented:
Yes they all work.
I tried all three of these formulas for text. They are all good. I prefer the last one because it gives a percentage of completeness. ie, 80% or 70% and 100% if every cell in range has DONE
Thanks
Chris
0

Group Finance ManagerCommented:
I meant using all formulas involved with the Text Function.
0

Author Commented:
No I have not, I don't know how to do that.
Any help is appreciated.
Thanks
Chris
0

Group Finance ManagerCommented:
It's just an idea but can you attach an example of your workbook.
0

Author Commented:
I'll make a dummy
0

Group Finance ManagerCommented:
OK, Chris
0

Author Commented:
Here is the dummy file.
I wish it was more simple, but 95% of the columns have formula and only few will have text.

So the new formula should work on both style if possible/
Thanks
Chris
Dummy-Done-100-.xlsx
0

Author Commented:
As I add more trainings, I will insert a column for that training before the "formula to calculate" column.
This Worksheet will grow and grow.
0

Author Commented:
Not sure why  it is working now.
I tried the formulas and none of them worked.
Now it is working fine.
Thanks Roy
Chris
0

Group Finance ManagerCommented:
0

Author Commented:
Is there an easy way to add a column??
It is a huge pain in the butt because there is a table in the bottom part.
Wont let me just add a column.
Thanks
In the dummy file I can add a column but in my large working sheet the insert is greyed out
0

Group Finance ManagerCommented:
If you make the upper data into a Table as well then you can add Columns independently to each Table
0

Author Commented:
Hmmm OK I will give it a try THanks
Chris
0

Group Finance ManagerCommented:
Here's the example. You need to just right click in the upper table and choose Insert Table Column
Dummy-Done-100-.xlsx
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.