Excel Formula Help

[Cassava]

I have info in 2 columns, one contains nine (9) different Provinces names all in different order and the Other Genders (male or females), also in varying order, In another column ....I want to put in a formula that could tell me for the Province of Ontario say, how many males there are and in another column....How many females and I want to be able to do this for all the provinces...........I thought an IFCOUNT formula would work, but I am having no luck can anyone help me please
<table x:str="" style="border-collapse: collapse; width: 138pt;" width="183" border="0" cellpadding="0" cellspacing="0"><col style="width: 34pt;" width="45"> <col style="width: 80pt;" width="106"> <col style="width: 24pt;" width="32"> <tbody><tr style="height: 40.5pt;" height="54"> <td class="xl27" style="height: 40.5pt; width: 34pt;" width="45" height="54">Prov</td> <td class="xl26" style="border-left: medium none; width: 80pt;" width="106">Child’s Given Name</td> <td class="xl27" style="border-left: medium none; width: 24pt;" width="32">Sex</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl28" style="height: 15.75pt; width: 34pt;" width="45" height="21">MB</td> <td class="xl25" style="border-left: medium none; width: 80pt;" width="106">Colter Vance James Charles</td> <td class="xl25" style="border-left: medium none; width: 24pt;" width="32">M</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl31" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">NL</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Mikhail Evdokimov</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">M</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">NW</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Jessica Caroline Cordeiro</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">F</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">NW</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Jeferson Amauri Cordeiro</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">M</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl30" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">NW</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Juliane Do Rocio Cordeiro</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">F</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl29" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">AB</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Tsebaot</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">F</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl29" style="border-top: medium none; height: 15.75pt; width: 34pt;" width="45" height="21">AB</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Rediet</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">F</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl28" style="height: 15.75pt; width: 34pt;" width="45" height="21">MB</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 80pt;" width="106">Haimanot Awoke</td> <td class="xl24" style="border-top: medium none; border-left: medium none; width: 24pt;" width="32">F</td> </tr> </tbody></table>

 

[xenou]

Sumproduct would be nice here:

book1
	A	B	C	D
1	Prov	Name	Sex
2	MB	Colte	M
3	NL	Mikha	M
4	NW	Jessi	F
5	NW	Jefer	M
6	NW	Julia	F
7	AB	Tseba	F
8	AB	Redie	F
9	MB	Haima	F
10
11		F	M
12	MB	1	1
13	NL	0	1
14	NW	2	1
15	AB	2	0
Sheet1

Formula in Cell B12:
=SUMPRODUCT(--($A$2:$A$9=$A12),--($C$2:$C$9="F"))

Formula in Cell C12:
=SUMPRODUCT(--($A$2:$A$9=$A12),--($C$2:$C$9="M"))

--Mutatis mutandis. Alex.

 

[xenou]

Cassava,
my favorite site to explain sumproduct is dead tonight, so if you are interested in learning more, start with the help files. One of its uses is just what you require, which is work with multiconditional statements. Since we are working with true and falses (which are binary 1s and 0s), we can count by "Summing" the "trues" (1s). The "--" in the formula turns these trues or falses into those 1s and 0s.

Regards,
Alex.

--if it comes back up, try this site tomorrow:
http://www.lqsystems.com/Excel/sumprod.asp

 

[Peter_SSs]

Another option is to use the built-in PivotTable feature (found under the data menu in versions before Excel 2007). The formatting will look better when you do it yourself than it does in this screen shot.

Post back if you want further help with how to create the PivotTable.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Prov	Childs Given Name	Sex		Count of Sex	Sex
2	MB	Colter Vance James Charles	M		Prov	F	M	Grand Total
3	NL	Mikhail Evdokimov	M		AB	2		2
4	NW	Jessica Caroline Cordeiro	F		MB	1	1	2
5	NW	Jeferson Amauri Cordeiro	M		NL		1	1
6	NW	Juliane Do Rocio Cordeiro	F		NW	2	1	3
7	AB	Tsebaot	F		Grand Total	5	3	8
8	AB	Rediet	F
9	MB	Haimanot Awoke	F
10
PivotTable

 

[Cassava]

Thank you so much...........This is fantastic information. I appreciate the help

 

[Cassava]

This is great, but I do need help with the pivot table. I like the look of it. Thanks for your advice

 

[Peter_SSs]

This is great, but I do need help with the pivot table. I like the look of it. Thanks for your advice

OK, try these steps ...

1. Select the data area, including headings (for my data, that was A1:C9)

2. Data|PivotTable and PivotChart Report...|Next|Next|Existing Worksheet: E1|Finish

Three things will probably appear...
- A blank PivotTable with sections 'Drop row fields here', 'Drop column fields here' and 'Drop data items here'.
- A 'PivotTable Field List' with your headings listed.
- A PivotTable Toolbar

3. For my data...
- Drag 'Prov' heading from the PivotTable Field List to 'Drop Row fields here'.
- Drag 'Sex' heading to 'Drop column fields here'.
- Drad 'Sex' heading again but to 'Drop data items here'.

4. At this point you should be able to close the PivotTable Field List and the PivotTable toolbar and you're finished! (Though you may find it useful to do a bit of research in the built-in Help and/or on this board for some more information about PivotTables)