Cell must have number before it is counted

[Shoota]

Hi All, I have lotto numbers totaled in A9-Lotto, A10-Bonus Ball, and A11-Power Ball. If cell A9 has 3, A10 has 1, A11 has 1 a total of 5. This would give me a prize of Power Ball 6 if counted as each cell, Because the total is 5 it could also be Power Ball 5 as that would be 4 Lotto and 1 Power Ball. I currently have this formula that would cover these cells. IF(B9+B11=5,$O$14,IF(B9+B10+B11=5,$O$15, ($O$14 shows Power 5 and $O$15 shows Power 6) I would like a formula that must count each cell as individual to get different results from 2 cell counts or 3 counts. Thanks

 

[Special-K99]

Does this work?

=IF((B9+B11)*(B9<>0)*(B11<>0)=5,$O$14,IF((B9+B10+B11)*(B9<>0)*(B10<>0)*(B11<>0)=5,$O$15))

So if B9+B11 = 5 AND B9 or B11 are zero the IF will fail then

If B9+B10+B11 = 5 AND B9 is zero or B10 is zero or B11 is zero the if will fail

 

[Shoota]

Does this work?

=IF((B9+B11)*(B9<>0)*(B11<>0)=5,$O$14,IF((B9+B10+B11)*(B9<>0)*(B10<>0)*(B11<>0)=5,$O$15))

So if B9+B11 = 5 AND B9 or B11 are zero the IF will fail then

If B9+B10+B11 = 5 AND B9 is zero or B10 is zero or B11 is zero the if will fail

Hello K99, Do you know if different years of excel make any difference, mine is 2016. It did not work on my sheet. I have attached the whole sheet to see if you may see something else that will solve the formula. Top you can see a formula from another helper on MrExcel. Thank You, Maurice

HelperSergio, =IF(NOT(A12),"",IF(A9+A11=7,$O$10,IF(A9+A10+A11=7,$O$11,IF(A9+A11=6,$O$12,IF(A9+A10+A11=6,$O$13,IF(A9+A11=5,$O$14,IF(A9+A10+A11=5,$O$15,IF(A9+A11=4,$O$16))))))))

These are my existing formulas, This seems to work ok.
Lotto, =IF(B9=6,$R$2,IF(B9+B10=6,$R$3,IF(B9=5,$R$4,IF(B9+F10=5,$R$5,IF(B9=4,$R$6,IF(B9+B10=4,$R$7,IF(B9=3,$R$8)))))))

PowerBall, =IF(C9+C11=7,$R$10,IF(C9+C10+C11=7,$R$11,IF(C9+C11=6,$R$12,IF(C9+C10+C11=6,$R$13,IF(C9+C11=5,$R$14,IF(C9+C10+C11=5,$R$15,IF(C9+C10+C11=4,$R$16)))))))
A B C D E F G H

7	21	25	33	36	40	38	7							Winning no's	Bonus Ball
2	7	3	8	1	27	8	7	5	8			Division	1	6			Lotto 1	2
16	21	12	17	6	29	12	11	9	13			Division	2	5	Yes		Lotto 2	3
20	25	26	21	12	32	16	17	13	18			Division	3	5			Lotto 3	4
31	33	30	25	18	34	22	23	17	23			Division	4	4	Yes		Lotto 4	5
35	36	33	31	28	37	28	29	21	28			Division	5	4			Lotto 5	6
38	40	38	35	29	39	34	35	27	33			Division	6	3	Yes		Lotto 6	7
7	7	7	7	7	7	9	7	8	7			Division	7	3			Lotto 7	8
0	6	1	2	0	0	0	1	1	1							Power Ball
1	0	1	0	0	0	0	0	0	0			Division	1	6		Yes	Power 1	10
1	1	1	1	1	1	0	1	0	1			Division	2	5	Yes	Yes	Power 2	11
FALSE	Lotto 1	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE			Division	3	5		Yes	Power 3	12
	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE			Division	4	4	Yes	Yes	Power 4	13
												Division	5	4		Yes	Power 5	14
												Division	6	3	Yes	Yes	Power 6	15
												Division	7	3		Yes	Power 7	16

<colgroup><col width="64" span="19" style="width:48pt"> </colgroup><tbody>
</tbody>