Sum a NULL cell

[ArcticWolf]

Hi,

I've got a complete mind block on how to get this right!

I have 5 values in cells a1:a5 - 1, 2, NULL, 4, and 5. When I try and sum
all 5 cells I get #value! Can I sum all five cells (12) even if there is a
NULL in a cell please?

Thanks in advance,

AW

 

[Gary''s Student]

=SUM(IF(ISNUMBER(A1:A5),A1:A5,""))

 

[Niek Otten]

Always post your formulas.

You probably don't mean #NULL as sometimes generated by Excel formulas,
because that would generate another #NULL.
You probably also don't mean an empty cell, as that would generate a numeric
answer.
You also don't seem to use the SUM formula; that would also ignore a
non-numeric entry.
So I assume you have a space or the word NULL in a cell and use a formula
like =A1+B1.
Use a real empty cell or use the SUM() function only, not individual
additions by the + operator

 

[ArcticWolf]

Thanks for your swift reply Gary - it worked a treat, just what I needed.

Thanks,

AW

 

[ob-server]

with gary's

control + shift + enter

 

[ArcticWolf]

You are correct Niek - I was actually using =sum(a1+a5+a9+a15) and I now
realise I may have misled you all. I was trying to make the explanation
easier (for me to write as opposed to you to understand!)

Thanks for taking the time to post.

AW

 

[Ashish Mathur]

Hi,

If you would like to sum up a range which has an error value, you can use
the following formula

-sumif(range,">0")+sumif(range,"<0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com