Text and numbers

**steviegee** · 07-02-2009, 08:01 AM

Hi all,

i wish to add the sum of 2 coulmns, however on some occasions some cells may contain text, in some instances both cells may contain text, is there a formula to get around this problem.

thanks
steviegee

**martindwilson** · 07-02-2009, 08:19 AM

Try
=if(and(istext(a1),istext(b1)),0,if(istext(a1),b1,if(istext(b1),a1,a1+b1)))

**steviegee** · 07-02-2009, 09:02 AM

Genius,

Many thanks,
thats solved the situation nicely.

Thanks for the tips, and i will read them.

regards
steviegee

**DonkeyOte** · 07-02-2009, 09:13 AM

Why not just use SUM ?

=SUM(A1:B1)

Non-numerics are simply ignored and the ranges need not be contiguous, ie:

=SUM(A1:B1,D1,Z10:AA12,AD1)

**steviegee** · 07-02-2009, 11:16 AM

Both formulas do the job,

however how can i get rid of the "false" errors if there is no values or text in the cells?

Thanks

**DonkeyOte** · 07-02-2009, 11:17 AM

Use SUM - it will simply return 0.

**steviegee** · 07-02-2009, 11:26 AM

I understand what your saying, but i do need the cells that have no values or text to return a blank anwser,they a1&b1 will have no text or values, it will not just be 1 cell that has a value or text.

basically it will be a block of data, then scroll down to find the next block of data.

hope your understanding me.

steviegee

**DonkeyOte** · 07-02-2009, 11:30 AM

No, I'm afraid I don't follow...

If you want a Null

=IF(COUNT(A1:B1),SUM(A1:B1),"")

Alternatively if the SUM of A1:B1 will never genuinely be 0 then you can use SUM as before but apply a Custom Format on the cells containing the formulae of: General;;
As such 0's display as Blanks.