Help needed - count number of cells based on multiple criteria

[JSLockwood]

Hi guys,

Can anybody help me with a formula[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Buy-In[/TD]
[TD]Players[/TD]
[TD]Places Paid[/TD]
[TD]1st(1)[/TD]
[TD]1st(2)[/TD]
[TD]1st(3)[/TD]
[TD]2nd[/TD]
[TD]3rd[/TD]
[TD]Pos[/TD]
[TD]Prize[/TD]
[TD]Profit /Loss[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]150[/TD]
[TD]50[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]-30[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]125[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]90[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]-15[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]55[/TD]
[TD]35[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]145[/TD]
[TD]35[/TD]
[TD]30[/TD]
[TD]35[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]35[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]70[/TD]
[TD]70[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]70[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]30[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]90[/TD]
[TD]50[/TD]
[TD]25[/TD]
[TD]25[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]50[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]90[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

Basically, these are poker tournament results. In this specific type of tournament, you randomly pick between 3 different prizes when you win.

I want to generate a formula that gives me the % time I have picked the "top" "middle" and "lowest" prize (to see any variance from the expected 1/3 on each). I want this individually for each of the different buy-in amounts, as well as as a whole.

So I need a formula that does the following:

% Top Prize Picked (for $15 buy-in):

When 'Column A' = 15 and 'Column I' = 1, count the amount of times Column D matches Column J (divided by when Column A = 15 and Column I = 1)

Thanks in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

I'm not understanding how columns E, F, G & H fit into the scheme. Can you explain further and/or give more calculation examples?

Based on the sample data and "When 'Column A' = 15 and 'Column I' = 1, count the amount of times Column D matches Column J (divided by when Column A = 15 and Column I = 1)", that result would be 0%. Is that correct?

 

[JSLockwood]

Welcome to the MrExcel board!

I'm not understanding how columns E, F, G & H fit into the scheme. Can you explain further and/or give more calculation examples?

Based on the sample data and "When 'Column A' = 15 and 'Column I' = 1, count the amount of times Column D matches Column J (divided by when Column A = 15 and Column I = 1)", that result would be 0%. Is that correct?

Thanks for your response Peter.

Columns G and H aren't particularly relevant to what I'm trying to do here, but just to be clear about what each column means I'll explain the process.

When you enter the tournament, you are seated at a table with a random number of players between 3 and 8. (This is shown in column B). The amount of places who receive a prize varies depending on how many players. Whoever wins the tournament has to then select one of 3 boxes, which becomes their prize.

Therefore, columns D, E and F show the 3 options for first prize, columns G and H show the prize for 2nd and 3rd place (if there is one).

Column I shows my position in that tournament and column J shows the prize I received.

You are correct that in my example, the calculation would be 0% - meaning out of all the times I won, I never picked the maximum prize available.

However, when I have a formula to work with I will be applying this in multiple ways as follows:

% picked max prize
% picked middle prize
% picked lowest prize

for each buy-in level as well as on the whole for all games. As mentioned, this will in effect show me how 'lucky' or 'unlucky' I am in terms of prize picking. This figure will help me to take account of variance when viewing my results.

 

[Peter_SSs]

You are correct that in my example, the calculation would be 0% - meaning out of all the times I won, I never picked the maximum prize available.

For that same sample data & buy-in of 15 & position of 1 ...
- What is your % for picking the middle prize, and which rows/cells in my screen shot below give you that %?
- What is your percentage for picking the lowest prize, and which rows/cells in my screen shot below give you that %?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Buy-In	Players	Places Paid	1st(1)	1st(2)	1st(3)	2nd	3rd	Pos	Prize	Profit /Loss
2	30	5	2	150	50	50	30	0	5	0	-30
3	15	4	1	125	20	20	0	0	1	20	5
4	15	8	2	90	40	20	25	0	4	0	-15
5	15	6	2	55	35	30	20	0	1	30	15
6	15	6	2	145	35	30	35	0	2	35	20
7	30	4	1	70	70	40	0	0	1	70	40
8	15	3	1	40	30	20	0	0	1	30	15
9	15	5	2	90	50	25	25	0	1	50	35
10	15	5	1	90	40	20	0	0	2	0	15
Prize %

 

[JSLockwood]

Grrr I've just encountered an issue, in that sometimes the top and middle, or middle and bottom prizes are the same. E.g. Row 2 and 3

Anyways, ignore that for now and I'll think of a work around, but the percentages based on the formula I'm trying to achieve would be:

Based on $15 buy in and first position (which happened 4 times) I picked:

Top prize: 0/4 - 0%
Middle prize: 3/4 (rows 3, 8 and 9) - 75%
Low prize: 2/4 (rows 3 and 5) - 50%

Yes, I know this is 125% for now

 

[Peter_SSs]

Yes, I know this is 125% for now

That is exactly why I was asking!

So, if you are happy with those percentages, try this formula, copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Buy-In	Players	Places Paid	1st(1)	1st(2)	1st(3)	2nd	3rd	Pos	Prize	Profit /Loss		Buy-In	15
2	30	5	2	150	50	50	30	0	5	0	-30		Pos	1
3	15	4	1	125	20	20	0	0	1	20	5		Top	0.00%
4	15	8	2	90	40	20	25	0	4	0	-15		Middle	75.00%
5	15	6	2	55	35	30	20	0	1	30	15		Low	50.00%
6	15	6	2	145	35	30	35	0	2	35	20
7	30	4	1	70	70	40	0	0	1	70	40
8	15	3	1	40	30	20	0	0	1	30	15
9	15	5	2	90	50	25	25	0	1	50	35
10	15	5	1	90	40	20	0	0	2	0	15
Prize %

 

[JSLockwood]

That looks great (as well as complicated lol). However, I need it in a specific place and my spreadsheet is quite a lot bigger.

I have linked a file here (I think) https://1drv.ms/x/s!Aj8pHu8tFy57aRkVGWwR1m64-H8

I want the figures for $15 buy-in in cells T26-28 and then copied left/right for the other buy-ins - if you know what I mean?

 

[Peter_SSs]

Try T26, copied across and down

=SUMPRODUCT(--($C$5:$C$29=T$18),--($L$5:$L$29=1),--(INDEX($F$5:$H$29,0,ROWS(T$26:T26))=$M$5:$M$29))/COUNTIFS($C$5:$C$29,T$18,$L$5:$L$29,1)