summing the numeric values of all cells but equals to zero if any cell contains any text...

[amrinderminhas]

Hello to all,
I have a little problem in using summation of the values which in normal case results in adding all values but if any one of the cell contains a text (any text, not specific) then the summation should result to zero.
I have applied this formula for values in cells from A2 to D2

[FONT=Arial, sans-serif]=IF(COUNTIF(A2:D2,"F"),0,IF(COUNTIF(A2:D2,"D"),0,IF(COUNTIF(A2:D2,"I"),0,SUM(A2:D2))))[/FONT]
[FONT=Arial, sans-serif]The above formula works only if particular text is input (such as here for F, D, I), But i have no idea to how to change this formula for any text input or even any cell having anything else than numeric value, then the sum of all cells (containing that particular cell) should result zero

thanx for your cooperation[/FONT]

 

[Eric W]

Welcome to the forum.

Maybe:

=IF(COUNT(A2:D2)=4,SUM(A2:D2),0)

COUNT returns the number of cells in the range containing numbers. So if it's 4, then all cells in the range are numbers. Empty cells will also return a 0.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe this...
=IF(COUNT(A2:D2)=4,SUM(A2:D2),0)

Hope this helps

M.

 

[amrinderminhas]

Yes, Coincidentally u both @Eric and @ Marcelo have given the same solution; now how it can then be incorrect.
This is the perfect solution i am finding, simple and accurate. I have tested it with various combinations and is found to be working for all situations. My heartly thanx to you both

 

[Eric W]

Glad we could help.