0

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.

Improve this question
3

3 Answers 3

Reset to default
5

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.

Improve this answer
3
  • +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, 2012 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, 2012 at 18:01
  • No, I agree that your suggested approach is better, just showing that it is possible with ISBLANK........
    – barry houdini
    Mar 14, 2012 at 18:46
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

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

I tried this on open office. May work.

Improve this answer
1
  • Two things: ISBLANK() in Excel only works on a single cell, and strings use "s, not 's.
    – Bob
    Mar 14, 2012 at 16:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .