Two Columns - And formula - getting count

Dana M

New Member
Joined
Jun 11, 2008
Messages
2
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

Excel Can Read to You
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
Joined
Sep 6, 2002
Messages
9,124
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"))
 
Upvote 0

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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...
 
Upvote 0

iliace

Well-known Member
Joined
Jan 15, 2008
Messages
3,546
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
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
 
Upvote 0

Forum statistics

Threads
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.
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