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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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
 

fluffy cabbage

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

New Member
Joined
Jun 3, 2002
Messages
9
Yeah, I see it. Not that bright tonight!!!

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

Fluffy
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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
Top