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

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

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

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



AdmiralAJ wrote:

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

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

On Tue, 19 Jun 2007 23:07:26 -0000, AdmiralAJ wrote:

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

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

On Jun 19, 4:51 pm, Ron Rosenfeld wrote:
On Tue, 19 Jun 2007 23:07:26 -0000, AdmiralAJ wrote:
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

Dave and Ron,

Thanks...both of those worked great!!