Two Columns - And formula - getting count

[Dana M]

I have two columns of Text; one is Type of Person (NI, C, IS) and the other is the gender of the person (M or F). I am trying to create a formula which will count how many, for example, NI-F, there are. So far no luck. I am quite new at Excel formulas and trying to reason it out. It should be simple, but just can not seem to get it right. Any ideas?

 

[DonkeyOte]

you can use sum product

assuming type in A, gender in B -- rows 1 to 100

to get NI-F

=SUMPRODUCT((A1:A100="NI")*(B1:B100="F"))

 

[Jonmo1]

Welcome to the board.

Sumproduct is the way to go for multi criteria counts...

=SUMPRODUCT(--(A1:A100="NI"),--(B1:B100="F"))

IMPORTANT RULES
Ranges cannot be entire column refs like A:A, must specify row #s like A1:A100
Ranges must be same size
You can replace the Text String Variables with cell references, like:

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

C1 = NI
D1 = F


Hope that helps...

 

[iliace]

Excel Workbook
	A	B	C	D	E	F
1	Type	Gender
2	NI	M
3	IS	F		Type	Gender	Count
4	NI	M		NI	F	3
5	NI	M		IS	F	5
6	NI	M		C	F	1
7	C	M		NI	M	7
8	NI	M		IS	M	4
9	C	M		C	M	5
10	IS	M			Total	25
11	IS	F
12	IS	M
13	IS	F
14	C	M
15	NI	F
16	NI	F
17	C	M
18	C	M
19	NI	M
20	C	F
21	IS	F
22	NI	M
23	IS	M
24	NI	F
25	IS	F
26	IS	M
Sheet2

 

[Dana M]

Thank you! I will try that.