Ignore text in SUM function

S

scottmmckay

I'm trying to create a count of two times selected from a drop down
which defaults to the value "Select Time"

so the cell that counts the two times says "#VALUE!" when the drop
down is set to "Select Time". How do I get it to ignore "Select Time"
and only count when there is a numaric value in the cell?
 
P

Peo Sjoblom

SUM ignores text, you get value errors if you are using operands like + - *
etc

=SUM(A2,B2)


--


Regards,


Peo Sjoblom
 
S

scottmmckay

that's the format I was using
=SUM(B6,B7)
Click to expand...

I kinda see part of my problem now, because I have 1 cell counting the
difference between the times in a given day(in hh:mm format) and I
have another cell converting that time to decimal format "=A3*24" and
I'm trying to add multiple days times together, but the cell that
tells me the difference between the times says "#VALUE!" when "Select
Time" is selected on the drop down... and the sum function doesn't
like it when "#VALUE!" is in one of the cells.

Is there any way to get past that?
 
P

Peo Sjoblom

=SUMIF(B6:B7,"<"&99^99)


will ignore cells that have errors but it would be better if you fixed the
formula
that returns the error

=IF(ISTEXT(A23),0,A23*24)



--


Regards,


Peo Sjoblom


that's the format I was using
=SUM(B6,B7)
Click to expand...

I kinda see part of my problem now, because I have 1 cell counting the
difference between the times in a given day(in hh:mm format) and I
have another cell converting that time to decimal format "=A3*24" and
I'm trying to add multiple days times together, but the cell that
tells me the difference between the times says "#VALUE!" when "Select
Time" is selected on the drop down... and the sum function doesn't
like it when "#VALUE!" is in one of the cells.

Is there any way to get past that?
 
S

scottmmckay

Fantastic, the If function definitely works better than the SUM
function in this case, great idea. thank you!
 
Top