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

0

5 Answers 5

7

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.

3
  • No horizontal scrollbar: nice! ;-)
    – Arjan
    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
    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
    Mar 3, 2011 at 4:17
2

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

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

2
  • Except that each cell is on a separate sheet...
    – mellamokb
    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)) Mar 14, 2012 at 18:52
1

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 agree to our terms of service, privacy policy and cookie policy

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