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.
--
David Biddulph

"Gene" wrote in message
...
here is what the formula looks like and I get an error #N/A

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

"Gene" wrote:

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


"Rick Rothstein" wrote:

Try this...

=SUMPRODUCT((A2:A1000=4)*(B2:B1000="Male")*(C2:C10 00=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).

--
Rick (MVP - Excel)


"gene" wrote in message
...
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.