Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
3  
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
    
possible duplicate [superuser.com/questions/252140/… –  Siva Charan Mar 14 '12 at 17:23

3 Answers 3

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.

share|improve this answer
    
+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

share|improve this answer
    
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.

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.