Formula to calculate a sum if non of the cells in a range are blank

[Juleskor]

Hi all,
So far I have been unsuccessful in trying to figure out a forumla that would check if any of the cells in a range (C4:C12) are blank, it would leave the total figure at C13 as "$0.00", but if ALL of the cells at C4:C12 contain anything else, the cell at C14 would calculate the sum of C13-30.
Background: I have 9 players in the U12 basketball team that I manage who pay $6 per game so that the total at C13 is $6x9 = $54. Each week the score sheet costs $30 so C14 = C13-30.
This is simple enough, but I need to be able to only calculate C14 as C13-30 if all of the 9 players pay for that week. If any of them don't pay, then the sum at C13 must equal $0.00.
Hope this makes sense and thanks in advance for any help.
Regards

[dredwolf]

try :
=IF(COUNTIF(C4:C12,">0")=ROWS(C4:C12),SUM(C13:C30),0)

Hope this helps

EDIT-
or
=IF(SUMPRODUCT(--(C4:C12<>""))=ROWS(C4:C12),SUM(C13:C30),0)

[Lemice]

Hello,

What you can use is to put a COUNTBLANK on C13, something like

Please Login or Register  to view this content.

[dredwolf]

@ Lemice, COUNTBLANK(), ISBLANK() only deal with truly empty cells...if a cell contains a formula that yields "", then it it considered NOT 'BLANK', and will be treated as such by those functions

[Lemice]

I tried to change any cell in my range to be ="" and the formula still works for me, eventhough if I ISBLANK it, it returns FALSE. Weird stuff ...

[dredwolf]

@ Lemice, MY Apologies, it seems COUNTBLANK does work properly, ISBLANK is the real pain one..again, sorry

[Lemice]

Don't be, this gives me a better understanding about COUNTBLANK and will help me deal with "" more efficiently.
Thanks for pointing it out actually.

[Juleskor]

Awesome!! Thank you all for the super-fast responses. I used the countblank method and it works like a treat. Brilliant.

[dredwolf]

Good that you got a solution !

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

and remeber to hit star '*" at bottom left of Lemice's post as well