# Sum only if any one of six cells have a value

I would like a cell (total) to sum 6 other cells only if one of those 6 cells have a value. If none of the six have a value i do not want anything to appear.

-
What if 2 or more cells have values? – Agrajag9 Mar 14 '12 at 14:44
@Agrajag9 I would assume they are part of the sum. – Bob Mar 14 '12 at 16:29
– Siva Charan Mar 14 '12 at 17:23

Blank if no cells contain data (i.e. all completely empty):

=IF(COUNTA(A1:A6),SUM(A1:A6), "")

Blank if no cells contain numbers (will also blank if they contain non-numerical data):

=IF(COUNT(A1:A6),SUM(A1:A6), "")

The difference is COUNT counts cells numbers only, while COUNTA counts cells that are not empty.

There's also a COUNTBLANK for ranges, which is what @alper.tekinalp attempted to do with ISBLANK. ISBLANK in Excel only supports checking a single cell, and will return TRUE for all ranges.

-
+1 @Bob I prefer the COUNT option but FYI it is possible to use ISBLANK with a range - but the result is an array and therefore formula needs to be "array entered", i.e.=IF(AND(ISBLANK(A1:A6)),"", SUM(A1:A6)) confirmed with CTRL+SHIFT+ENTER – barry houdini Mar 14 '12 at 17:26
I've never heard of Excel array formulae before ;) Though, in this case, I see no advantage to using AND(ISBLANK()) over COUNTBLANK(). – Bob Mar 14 '12 at 18:01
No, I agree that your suggested approach is better, just showing that it is possible with ISBLANK........ – barry houdini Mar 14 '12 at 18:46

I am assuming that you may even have negative numbers at times, and this will not work if the inputs are not numbers. Also am assuming you meant, you would like a total if there is a number in ANY of the 6 cells.

=IF(SUM(A1:A6)<>0,SUM(A1:A6),"")

or since positive and negatives would be entries but total zero perhaps

=IF(COUNTA(A1:A6)<>0,SUM(A1:A6),"")

even a textual entry would be noticed but not add to a total. Not sure if that is acceptable either

-
But this will fail if the sum is actually 0, e.g. 4 + 2 + -4 – Bob Mar 14 '12 at 16:14
good point -4+4 would both be in the cells, I adjusted answer – datatoo Mar 14 '12 at 16:29
Argh, I can't do maths.. I meant 4 + -4.. Anyway, nice fix. – Bob Mar 14 '12 at 16:36
=IF(NOT(ISBLANK(A1:F1)),VALUE(SUM(A1:F1)),'')

I tried this on open office. May work.

-
Two things: ISBLANK() in Excel only works on a single cell, and strings use "s, not 's. – Bob Mar 14 '12 at 16:30