# COUNTIF Formula in Excel08 for Mac

#### fluffy cabbage

##### New Member
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!

Fluffy

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
This was done in windows, but hopefully should be the same for the mac.

Excel Workbook
ABCDE
1YSYMYLS
2DivA3221
3DivB1001
4DivC3221
5DivD0001
6DivE4221
7
8PlayerDivisionSize
9nameDivAYS
10nameDivAYM
11nameDivAYL
12nameDivAS
13nameDivAYS
14nameDivAYS
15nameDivAYM
16nameDivAYL
17nameDivBS
18nameDivBYS
19nameDivCYS
20nameDivCYM
21nameDivCYL
22nameDivCS
23nameDivCYS
24nameDivCYS
25nameDivCYM
26nameDivCYL
27nameDivDS
28nameDivEYS
29nameDivEYS
30nameDivEYM
31nameDivEYL
32nameDivES
33nameDivEYS
34nameDivEYS
35nameDivEYM
36nameDivEYL
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

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

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

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

Fluffy

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

Replies
13
Views
359
Replies
7
Views
187
Replies
1
Views
686
Replies
10
Views
434
Replies
3
Views
416

1,217,347
Messages
6,136,045
Members
449,982
Latest member
josephinelinnea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back