Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero. I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's driving
me mad!!!!

Can anyone out there help????

=IF(SUMPRODUCT(--(ISNUMBER(B6:E6)))0,SUM(B6:E6),"")

Regards,
Stefi

€žMar_W€� ezt Ã*rta:

Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero. I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's driving
me mad!!!!

Can anyone out there help????

Hi

=IF(SUM(--ISBLANK(B6:E6))0,"",SUM(B6:E6))
, entered as an array formula (Ctrl+Shift+Enter)

Or
=IF(OR(B6="",C6="",D6="",E6=""),"",SUM(B6:E6))
, entered in usual way (non-array formula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Mar_W" wrote in message
...
Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero.
I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's
driving
me mad!!!!

Can anyone out there help????

Try =IF(COUNT(B6:E6),SUM(B6:E6),"")
This is NOT an array formula
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mar_W" wrote in message
...
Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero.
I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's
driving
me mad!!!!

Can anyone out there help????

Thanks Bernard - that works fine. I sometimes need to sum up values from a
hlookup table and again get zeros with blank cells.

ie -
=SUM(HLOOKUP(BB$3,North_Water_SC,$B19,FALSE))+(HLO OKUP(BB$3,North_Waste_SC,$L15,FALSE))+(HLOOKUP(BB$ 3,North_Long_Cycle,$V9,FALSE))

Have you any ideas for this? Thanks for your help, Marianne

"Bernard Liengme" wrote:

Try =IF(COUNT(B6:E6),SUM(B6:E6),"")
This is NOT an array formula
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mar_W" wrote in message
...
Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero.
I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's
driving
me mad!!!!

Can anyone out there help????

1. Do you mean if the source B6:E6 are all blank, the result must show blank.
2. How about if some are blank and some have value ? What result u need ?
You still need them to be summed up or not.
I believe you gave a good importance of your source data when they are
blank...

try this where the formula will not give a summed value if one cell is blank.

=if(or(b6="",c6="",d6="",e6=""),"",sum(b6:e6))
or
=if(or(b6="",c6="",d6="",e6=""),"incomplete data",sum(b6:e6))

for lookup, try to read the help files for the function limitations.

happy holidays.

"Mar_W" wrote:

Thanks Bernard - that works fine. I sometimes need to sum up values from a
hlookup table and again get zeros with blank cells.

ie -
=SUM(HLOOKUP(BB$3,North_Water_SC,$B19,FALSE))+(HLO OKUP(BB$3,North_Waste_SC,$L15,FALSE))+(HLOOKUP(BB$ 3,North_Long_Cycle,$V9,FALSE))

Have you any ideas for this? Thanks for your help, Marianne

"Bernard Liengme" wrote:

Try =IF(COUNT(B6:E6),SUM(B6:E6),"")
This is NOT an array formula
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mar_W" wrote in message
...
Hi, I'm trying to put a simple formula in excel to add up some cells - the
trouble is when the cells are blank, excel still calculates it as a zero.
I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns zero.
You'd think it would be something simple but I can't see it and it's
driving
me mad!!!!

Can anyone out there help????

Hi

In general
=IF(ISERROR(HLOOKUP(...)),ResponseWhenError,HLOOKU P(...))
or
IF(ISNA(HLOOKUP(...)),ResponseWhenNA,HLOOKUP(...))


Arvi Laanemets


"Mar_W" wrote in message
...
Thanks Bernard - that works fine. I sometimes need to sum up values from
a
hlookup table and again get zeros with blank cells.

ie -

=SUM(HLOOKUP(BB$3,North_Water_SC,$B19,FALSE))+(HLO OKUP(BB$3,North_Waste_SC,$
L15,FALSE))+(HLOOKUP(BB$3,North_Long_Cycle,$V9,FAL SE))

Have you any ideas for this? Thanks for your help, Marianne

"Bernard Liengme" wrote:

Try =IF(COUNT(B6:E6),SUM(B6:E6),"")
This is NOT an array formula
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Mar_W" wrote in message
...
Hi, I'm trying to put a simple formula in excel to add up some cells -
the
trouble is when the cells are blank, excel still calculates it as a
zero.
I
know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain
true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns
zero.
You'd think it would be something simple but I can't see it and it's
driving
me mad!!!!

Can anyone out there help????

Nel post
*Mar_W* ha scritto:

Hi, I'm trying to put a simple formula in excel to add up some cells
- the trouble is when the cells are blank, excel still calculates it
as a zero. I know I can use the following to ignore the blanks
-=IF(SUM(B6:E6)=0,"",SUM(B6:E6))- but sometimes cells B6 to E6 might
contain true zero scores. I've also tried using
if(isblank(sum(B6:E6)),"",(SUM(B6:E6))) but again it still returns
zero. You'd think it would be something simple but I can't see it and
it's driving me mad!!!!

Can anyone out there help????


Hi Mar_W

try this one:

=IF(COUNTA(B6:E6),SUM(IF(B6:E6<"",B6:E6,"")),"")

array entered, i.e. with Ctrl+Shift+Enter instead of Enter.

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy