Summing cells with no value ("") and obtaining ""

[carlsondaniel]

I have a column of cells that if there is no value in any of the cells
(""), It needs to return "". When I sum the cells, It still returns a
"0" even when there is nothing in cells. Any suggestions? Thanks.

 

[Ragdyer]

One way:

=IF(SUM(F1:F10),SUM(F1:F10),"")

 

[Dave Peterson]

=if(count(a:a)=0,"",sum(a:a))

If you have at least one number in column A, you'll get the sum.

 

[MartinW]

Hi Ragdyer,

Could you please explain your formula. I can see that it
works, but to my mind your logical test SUM(F1:F10) doesn't
seem to be complete yet it works.
I would have thought that the logical test should be SUM(F1:F10)=0
which doesn't work.
Can you explain that for me?

Regards
Martin

 

[RagDyeR]

In your travels through these groups, you might have noticed conversations
where logicals (true, false) can be represented by zero and one.
Sumproduct converts true and false to 1 and 0 using math operators, and
Vlookup can use 1 or 0 as the 4th argument in place of true and false to
find exact or approximate matches.

Actually, false is 0, BUT true *can/may* be anything else!
*Any* number in the 4th argument of Vlookup() will call for an approximate
match.

The formula I suggested:

=IF(SUM(F1:F10),SUM(F1:F10),"")

Is a simple - <<<If true, do the first action -sum(f1:f10) , - If false, do
second instruction - (display zero length string "" )>>>

So, if sum(f1:f10) returns zero, the IF() function equates that zero to
FALSE, and performs the second instruction, show null "".

And if sum(f1:f10) returns *anything else*, the IF() function equates that
*non-zero* to TRUE, and performs the first instruction, total f1:f10.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi Ragdyer,

Could you please explain your formula. I can see that it
works, but to my mind your logical test SUM(F1:F10) doesn't
seem to be complete yet it works.
I would have thought that the logical test should be SUM(F1:F10)=0
which doesn't work.
Can you explain that for me?

Regards
Martin

 

[MartinW]

Thanks RD,

I guess that means that all those =0s that I've put in over the years
were really superfluous. I suppose that doesn't really matter
but it's still good to know.

Thanks again
Martin