Help with inconsistent calculated column formula error

[Liz_I3]

Hi
I am working with Excel 2007. I have the below forumla in a cell in a table.

=SUM(COUNTIF(Y173:BC173,{"A","B","V","T","STAT","JD","STD"}))+SUM(Y173:BC173)+(COUNTIF(Y173:BC173,"VHD")/2)

The forumla returns the correct answer, but when a new row is added the formula is automatically changed to

=SUM(COUNTIF(Y174:BC174,{0,0,0,0,0,0,0}))+SUM(Y174:BC174)+(COUNTIF(Y174:BC174,"VHD")/2)

Also my original formula is getting and inconsistent calculated column formula error even though it is returning what I expected it to return. What excel changes it to, returns an incorrect value.

How can I solve this?

Thanks
L

 

[hallingh]

Maybe I'm misunderstanding the problem, but wouldn't this solve it?

=SUM(COUNTIF(Y$173:BC$173,{"A","B","V","T","STAT","JD","STD"}))+SUM(Y$173:BC$173)+(COUNTIF(Y$173:BC$173,"VHD")/2)

Hank

 

[Liz_I3]

Yes, that work's and I believe that is exactly what I do have in the initial table, but when you add a new row to the table the formula is automatically put in the new rows column and in that cell if drops the "A", "B", "STA" etc and replaces them with 0. I do not understand why this would happen. Should the formula not be copied exactly whether excel thinks it is correct or not?

Thanks for looking

L