1

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

6

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
2

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
  • 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
1

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
0

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

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.