# Sum if ONLY all the cells have a value?

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

## 5 Answers

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.

• No horizontal scrollbar: nice! ;-) Commented Mar 2, 2011 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. Commented Mar 2, 2011 at 11:10
• Further suggestion: `COUNTA()` will count cells with numbers or text, `COUNT()` will only count cells with numbers. Commented Mar 3, 2011 at 4:17

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))))))))))
``````
• It tells me I have entered to many arguments (9 Ifs in total) for this function. Commented Mar 2, 2011 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. Commented Mar 2, 2011 at 10:18
• 7 nested ifs is the limit Commented Mar 3, 2011 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 Commented Mar 14, 2012 at 19:16

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

• Except that each cell is on a separate sheet... Commented Mar 9, 2011 at 5:45
• 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))` Commented Mar 14, 2012 at 18:52

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))
``````

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