Sum A Range only if no text is present in the range

[AdmiralAJ]

I'm trying to create a formula to sum a range of cells, like A1:A10,
but only if all the values are numbers. On occassion I may place the
text "TBD" into a cell and if thats the case I want to sum of the
range to be zero.

This is the latest formula I tried but it didn't work. Any thoughts
would be greatly appreciated.

=SUM(IF(ISTEXT(A1:A10),0,A1:A10))

AJ

 

[Dave Peterson]

=if(countif(a1:a10,"TBD")>0,0,sum(a1:a10))
or
=sum(A1:A10)*(Countif(a1:a10,"TBD")=0)

 

[Ron Rosenfeld]

I'm trying to create a formula to sum a range of cells, like A1:A10,
but only if all the values are numbers. On occassion I may place the
text "TBD" into a cell and if thats the case I want to sum of the
range to be zero.

This is the latest formula I tried but it didn't work. Any thoughts
would be greatly appreciated.

=SUM(IF(ISTEXT(A1:A10),0,A1:A10))

AJ

Dave's formulas are simpler, but you are very close.

=SUM(IF(OR(ISTEXT(A1:A10)),0,A1:A10))

entered as an **array** formula will do the job, also. To enter an **array**
formula, you must hold down <ctrl><shift> while hitting <enter>. Excel will
place braces {...} around the formula.
--ron

 

[AdmiralAJ]

Dave's formulas are simpler, but you are very close.

=SUM(IF(OR(ISTEXT(A1:A10)),0,A1:A10))

entered as an **array** formula will do the job, also. To enter an **array**
formula, you must hold down <ctrl><shift> while hitting <enter>. Excel will
place braces {...} around the formula.
--ron

Dave and Ron,

Thanks...both of those worked great!!