Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    

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

You must log in to answer this question.

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