COUNTIF Formula in Excel08 for Mac

[fluffy cabbage]

Hi there,

I have a Players database for a sports association I run - in the database, among other information, I have listed what team(s) each child is playing on and what tshirt size they will need for each team.

I am using the COUNTIF formula to determine how many players I have in each division. I would now like to count how many of each tshirt size in each division I need - so I need a formula that basically says something like COUNTIF Jane is in T-ball AND wears a YS shirt. I've tried several different things, but I'm not coming up with the right result.
There are some of the same shirt sizes across different divisions, so I need to be able to count for each division, not just total of one size.

I hope this is explained clearly enough!

Thanks in advance,
Fluffy

 

[jasonb75]

This was done in windows, but hopefully should be the same for the mac.

Excel Workbook
	A	B	C	D	E
1		YS	YM	YL	S
2	DivA	3	2	2	1
3	DivB	1	0	0	1
4	DivC	3	2	2	1
5	DivD	0	0	0	1
6	DivE	4	2	2	1
7
8	Player	Division	Size
9	name	DivA	YS
10	name	DivA	YM
11	name	DivA	YL
12	name	DivA	S
13	name	DivA	YS
14	name	DivA	YS
15	name	DivA	YM
16	name	DivA	YL
17	name	DivB	S
18	name	DivB	YS
19	name	DivC	YS
20	name	DivC	YM
21	name	DivC	YL
22	name	DivC	S
23	name	DivC	YS
24	name	DivC	YS
25	name	DivC	YM
26	name	DivC	YL
27	name	DivD	S
28	name	DivE	YS
29	name	DivE	YS
30	name	DivE	YM
31	name	DivE	YL
32	name	DivE	S
33	name	DivE	YS
34	name	DivE	YS
35	name	DivE	YM
36	name	DivE	YL
Sheet1

Then use the formula
=SUMPRODUCT(($B$9:$B$36=$A2)*($C$9:$C$36=B$1))
In B2 and autofill the rest of the table.

Hope this helps

 

[fluffy cabbage]

Hm, I'm getting a #VALUE! now.

I'm using:

=SUMPRODUCT(('2009 Players'!N2:P2+'2009 Players'!N2:P200=A3)*('2009 Players'!O2:Q200=F2))

What am I doing wrong?

Thank you!!

Fluffy

 

[fluffy cabbage]

Yeah, I see it. Not that bright tonight!!!

Thanks so much for your quick help, I really needed it!

Fluffy

 

[jasonb75]

Haha, sometimes it's too easy to overlook the obvious

Just in case you have more problems

=SUMPRODUCT(('2009 Players'!$N$2:$P$200=$A3)*('2009 Players'!$O$2:$Q$200=F$2))

Should be what you need