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

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

--
Gary''s Student - gsnu200817

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

Thanks,

AW

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200817

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ArcticWolf" wrote in message
...
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

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

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ArcticWolf" wrote in message
...
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

with gary's

control + shift + enter


"ArcticWolf" wrote:

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

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

"ArcticWolf" wrote in message
...
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