Sum only if any one of six cells have a value

Question

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.

Solution 1

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.

Solution 2

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

Solution 3

=IF(NOT(ISBLANK(A1:F1)),VALUE(SUM(A1:F1)),'')

I tried this on open office. May work.

Comments

[Vijay]

What if 2 or more cells have values?

[tvdo]

@Agrajag9 I would assume they are part of the sum.

[Siva Charan]

possible duplicate http://superuser.com/questions/252140/sum-if-only-all-the-ce‌​lls-have-a-value

[tvdo]

But this will fail if the sum is actually 0, e.g. 4 + 2 + -4

[datatoo]

good point -4+4 would both be in the cells, I adjusted answer

[tvdo]

Two things: ISBLANK() in Excel only works on a single cell, and strings use "s, not 's.

[tvdo]

Argh, I can't do maths.. I meant 4 + -4.. Anyway, nice fix.

[barry houdini]

+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

[tvdo]

I've never heard of Excel array formulae before ;) Though, in this case, I see no advantage to using AND(ISBLANK()) over COUNTBLANK().

[barry houdini]

No, I agree that your suggested approach is better, just showing that it is possible with ISBLANK........