Count using multiiple Criteria

[gene]

I am trying to count the number of time a text srting appers. But, only if
it meets two other criterias. For Example I have three columb a is rank,
columb B is Gender and columb c is building. I want to count the number of
times a particular rank appears in columb a but only if the gender is Male
and the building is 2071.

Can any one help me? By the way I need to do this in Excel 2003.

 

[T. Valko]

Try this:

Use cells to hold your criteria.

E1 = the rank to count
F1 = male
G1 = 2701

=SUMPRODUCT(--(A2:A100=E1),--(B2:B100=F1),--(C2:C100=G1))

 

[Rick Rothstein]

Try this...

=SUMPRODUCT((A2:A1000=4)*(B2:B1000="Male")*(C2:C1000=2071))

Change each of the ranges to cover the maximum number of cells you will need
to use this formula over and the 4 in the first logical expression is an
assumed rank number (change that as necessary).

 

[Gene]

this is what my spread sheet looks like




Rank Name Gender BLDG Room PLT
CPL Lee, P M 2071 A202A 3rd
SPC McDanile F 2071 A303B 3rd
PVT Smith, J M 2071 C101A 2nd
PV2 Powell, D F 2071 D103B 1st

 

[Gene]

this is what my spreadsheet looks like

Rank Name Gender BLDG Room PLT
CPL Lee, P M 2071 A202A 3rd
SPC McDanile F 2071 A303B 3rd
PVT Smith, J M 2071 C101A 2nd
PV2 Powell, D F 2071 D103B 1st

 

[Gene]

here is what the formula looks like and I get an error #N/A

=SUMPRODUCT((A19:A142="CPL")*(B19:B142="M")*(C2:C142=2071))

 

[T. Valko]

=SUMPRODUCT((A19:A142="CPL")*(B19:B142="M")*(C2:C142=2071))

All ranges must be the same size. The third range is not the same size as
the other ranges.

 

[Rick Rothstein]

It looks like the columns you told us in your original post are not the
columns you are actually using for those items on the worksheet. On top of
that, the last logical expression needs to cover the same number of cells as
the other logical expressions. If I am interpreting the column locations
correctly, try this instead...

=SUMPRODUCT((A19:A142="CPL")*(C19:C142="M")*(D19142=2071))