Countif formula

[Makat]

Hello,
I have a range of cells containing numbers 1-4 or some may have the letters "na". I am using a formula to count all the cells that contain a number and then multiply that number by another cell. Formula is:
=(COUNTIF(I5:CA5,">=0")-COUNTIF(I5:CA5,">=5"))*H5.
I have been asked not to include cell AC5 in that count and I don't know how to modify the formula to exclude that one cell.
I appreciate any help!!!! Thanks,
Makat

 

[Dan Waterloo]

Try

=((COUNTIF(I5:CA5,">=0")
- countif(AC5, ">=0")
- COUNTIF(I5:CA5,">=5")
+ countif(AC5, ">=5")) * H5

 

[Dan Waterloo]

You might also want to check out this page on the site to see how countif can handle multiple crieria: http://www.mrexcel.com/td0128.html

 

[Makat]

That looks like it will work!
How would I use the same concept to exclude cell AC5 if I were adding up all the numbers in a range and then dividing that number by another cell?
The current formula is: =SUM(I5:DO5)/DR5

 

[Dan Waterloo]

Try
=(SUM(I5:DO5) - AC5)/DR5

 

[Diffy]

=(sum(I5:DO5)-AC5)/DR5

 

[Dan Waterloo]

Or another way:
=(SUM(I5:AB5) + SUM(AD5:DO5))/DR5

 

[Makat]

That works but if cell AC5="na" then it results as #VALUE!

 

[Makat]

Thanks Dan, that solved it!
Have a great day!
Makat