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.


Many thanks



5 Answers 5


Assuming the data is in A1 to 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
    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.
    – RocketGoal
    Commented Mar 2, 2011 at 11:10
  • 1
    Further suggestion: COUNTA() will count cells with numbers or text, COUNT() will only count cells with numbers.
    – Bavi_H
    Commented Mar 3, 2011 at 4:17

The IF function works like this:


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:

  • It tells me I have entered to many arguments (9 Ifs in total) for this function.
    – RocketGoal
    Commented Mar 2, 2011 at 9:59
  • 1
    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
    Commented Mar 2, 2011 at 10:18
  • 7 nested ifs is the limit
    – datatoo
    Commented Mar 3, 2011 at 16:41
  • 2
    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...
    – mellamokb
    Commented Mar 9, 2011 at 5:45
  • 2
    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:


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

You must log in to answer this question.

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