# Two Columns - And formula - getting count

#### Dana M

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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### DonkeyOte

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Well-known Member
Excel Workbook
ABCDEF
1TypeGender
2NIM
3ISFTypeGenderCount
4NIMNIF3
5NIMISF5
6NIMCF1
7CMNIM7
8NIMISM4
9CMCM5
10ISMTotal25
11ISF
12ISM
13ISF
14CM
15NIF
16NIF
17CM
18CM
19NIM
20CF
21ISF
22NIM
23ISM
24NIF
25ISF
26ISM
Sheet2

#### Dana M

##### New Member
Thank you! I will try that.

Replies
5
Views
248
Replies
4
Views
135
Replies
3
Views
172
Replies
3
Views
143
Replies
4
Views
196

1,191,693
Messages
5,988,131
Members
440,126
Latest member
Dee8

### 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