COUNTIF Formula in Excel08 for Mac

fluffy cabbage

New Member
Joined
Jun 3, 2002
Messages
9
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top