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.

 

[David Biddulph]

You said that column A was rank and column B was gender, but in your example
you seem to have a name between.
Either change your column references accordingly, or if column A contains
name as well as rank you may want
=SUMPRODUCT((LEFT(A19:A142,3)="CPL")*(B19:B142="M")*(C19:C142=2071))

Note also that your ranges need to be the same length, so I changed C2:C142
to C19:C142 to match the other 2 columns.

 

[xlmate via OfficeKB.com]

I tried both of these formula and its working.

=SUMPRODUCT((A2:A21="CPL")*(C2:C21="M")*(D221=2071))

or

=SUMPRODUCT(--(A2:A20="CPL"),--(C2:C20="M"),--(D220=2071))

based on your datatable , your col's ref is totally wrong
try adjusting the col's references.

HTH

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

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

this is what my spreadsheet looks like

[quoted text clipped - 20 lines]

 

[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))