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.

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

--
Biff
Microsoft Excel MVP


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

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.

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

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.

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.

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

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


--
Biff
Microsoft Excel MVP


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

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.

I tried both of these formula and its working.

=SUMPRODUCT((A2:A21="CPL")*(C2:C21="M")*(D2:D21=20 71))

or

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

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

HTH



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

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

this is what my spreadsheet looks like

[quoted text clipped - 20 lines]

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200812/1

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")*(D19:D 142=2071))

--
Rick (MVP - Excel)


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