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 need to sum 9 cells of data, each one on a separate sheet, but always in the same location. I only want a figure returned when all the cells have data, even if it's a 0. But, if one of the cells is blank I want a blank return.

I'm trying to get my head around, making it up actually, where the IFs and ISBLANK and SUMS would go. Any pointers would be greatly appreciated.

SUM(IF(ISBLANK(RANGEA,OR(RANGEB),0,ALLRANGES)))

Many thanks

Michael

share|improve this question
add comment

5 Answers

up vote 6 down vote accepted

Assuming the data is in A1 to A9:

=IF(COUNTA(A1,A2,A3,A4,A5,A6,A7,A8,A9)<9,"",SUM(A1,A2,A3,A4,A5,A6,A7,A8,A9))

And yes, this will work for cells on different sheets, even files, just change the reference, make sure you change it in both parts of the formula though.

share|improve this answer
    
No horizontal scrollbar: nice! ;-) –  Arjan Mar 2 '11 at 10:45
    
Tried it and it works - off to put it to the real test now though. Wonderful use of COUNTA (I use that all the time but never thought of it like that!). Cheers. –  RocketGoal Mar 2 '11 at 11:10
    
Further suggestion: COUNTA() will count cells with numbers or text, COUNT() will only count cells with numbers. –  Bavi_H Mar 3 '11 at 4:17
add comment

The IF function works like this:

IF(TEST, DO_IF_TRUE, DO_IF_FALSE)

Unfortunately the ISBLANK function only works for one cell, so you have to nest IF statements like this:

=IF( ISBLANK(CELL1) , " " , IF( ISBLANK(CELL2), " ", IF( ISBLANK(CELL3), " ", SUM(RANGE) ) ) )

That will check if the first cell is blank. If so it will print a blank space. If not, it will test if the second cell is blank, print a blank space if so, and continue testing if not. At the end, it prints the SUM if all are blank.

So, with 9 cells it would look like this:

=IF(ISBLANK(A1),"",IF(ISBLANK(A2),"",IF(ISBLANK(A3),"",IF(ISBLANK(A4),"",IF(ISBLANK(A5),"",IF(ISBLANK(A6),"",IF(ISBLANK(A7),"",IF(ISBLANK(A8),"",IF(ISBLANK(A9),"",SUM(A1:A9))))))))))
share|improve this answer
    
It tells me I have entered to many arguments (9 Ifs in total) for this function. –  RocketGoal Mar 2 '11 at 9:59
    
I just tested this with 9 cells and it works fine. The "too many arguments" indicates you have a stray comma or parenthesis somewhere. I'll add an example that checks 9 cells to make it a little easier. –  Patches Mar 2 '11 at 10:18
    
7 nested ifs is the limit –  datatoo Mar 3 '11 at 16:41
    
It's probably a moot point given the other answers here but 7 nested functions is the limit in Excel 2003 - in Excel 2007 and later you can use up to 64 –  barry houdini Mar 14 '12 at 19:16
add comment

In supplement to nnewton's answer above, you could reformat as =IF(COUNTA(A1:A9)<9,"",SUM(A1:A9)) with the same result

share|improve this answer
    
Except that each cell is on a separate sheet... –  mellamokb Mar 9 '11 at 5:45
1  
You can use COUNTA with a "3d" reference, though, (to apply across a range of sheets), so assuming cell B3 on sheets 1 to 9 you can use this version of Diem's suggested formula =IF(COUNTA(Sheet1:Sheet9!B3)<9,"",SUM(Sheet1:Sheet9!B3)) –  barry houdini Mar 14 '12 at 18:52
add comment

You could also do it using this:

=IF(OR(ISBLANK(A1),ISBLANK(A2),ISBLANK(A3),ISBLANK(A4),ISBLANK(A5),ISBLANK(A6),ISBLANK(A7),ISBLANK(A8),ISBLANK(A9)),"",SUM(A1:A9))
share|improve this answer
add comment

Use an array formula {sum(a1:a10)*1}. (Use Ctrl+Shift+Enter to get the outside braces.)

share|improve this answer
add comment

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.