Text and numbers

steviegee · #1 07-02-2009, 07: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 · #2 07-02-2009, 07:19 AM

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

steviegee · #3 07-02-2009, 08:02 AM

Genius,

Many thanks,
thats solved the situation nicely.

Thanks for the tips, and i will read them.

regards
steviegee

DonkeyOte · #4 07-02-2009, 08: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 · #5 07-02-2009, 10: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 · #6 07-02-2009, 10:17 AM

Use SUM - it will simply return 0.

steviegee · #7 07-02-2009, 10: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 · #8 07-02-2009, 10: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.

steviegee · #9 07-02-2009, 10:34 AM

Problem solved.

Perfect.

Many thanks

Steviegee

Welcome to the Excel Forum

Forum Statistics