# 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

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! ;-) – 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

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... – mellamokb Mar 9 '11 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))` – barry houdini Mar 14 '12 at 18:52

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. – 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

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