I want to count # of times a value(name) appears in a column IF, in another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

On the example, 'A1' & 'A6' should be 'A' & 'E' - thx

"Richard" wrote:

I want to count # of times a value(name) appears in a column IF, in another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100="yes"))

In article ,
Richard wrote:

I want to count # of times a value(name) appears in a column IF, in another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

Try this:

=SUMPRODUCT(--(A1:A10="Joe"),--(B1:B10="Yes"))

Biff

"Richard" wrote in message
...
I want to count # of times a value(name) appears in a column IF, in another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

I have 50+ employees whose names I add to my "tracking" worksheet and I want
to quantify all of my data on my "analysis" sheet, so I'm getting data from
another sheet - don't know if this matters. I tried this:
=SUMPRODUCT(--('2007_Corrective'!C:C="LAST,
FIRST"),--('2007_Corrective'!F:F="yes"))
.... and I get #NUM! error.

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100="yes"))

In article ,
Richard wrote:

I want to count # of times a value(name) appears in a column IF, in another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

You can't use entire columns as range references unless you're using Excel
2007.

Use a smaller range. Also, the ranges must be of equal size.

Biff

"Richard" wrote in message
...
I have 50+ employees whose names I add to my "tracking" worksheet and I
want
to quantify all of my data on my "analysis" sheet, so I'm getting data
from
another sheet - don't know if this matters. I tried this:
=SUMPRODUCT(--('2007_Corrective'!C:C="LAST,
FIRST"),--('2007_Corrective'!F:F="yes"))
... and I get #NUM! error.

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100="yes"))

In article ,
Richard wrote:

I want to count # of times a value(name) appears in a column IF, in
another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

Thanks! That answered the question.

"T. Valko" wrote:

You can't use entire columns as range references unless you're using Excel
2007.

Use a smaller range. Also, the ranges must be of equal size.

Biff

"Richard" wrote in message
...
I have 50+ employees whose names I add to my "tracking" worksheet and I
want
to quantify all of my data on my "analysis" sheet, so I'm getting data
from
another sheet - don't know if this matters. I tried this:
=SUMPRODUCT(--('2007_Corrective'!C:C="LAST,
FIRST"),--('2007_Corrective'!F:F="yes"))
... and I get #NUM! error.

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100="yes"))

In article ,
Richard wrote:

I want to count # of times a value(name) appears in a column IF, in
another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks

Well, yes, the string you try to match does matter. *And*, except in
XL07, you can't use entire columns in array formulae, which SUMPRODUCT()
formulae are.

It's hard to know what to suggest without knowing whether you want to
quantify all of your data by name (in which case a pivot table would
probably be best), or to quantify all of your data by having a name in
the column C, in which case you could use something like

=SUMPRODUCT(--('2007_Corrective'!C1:C65000<""),--(F1:F65000="yes"))




In article ,
Richard wrote:

I have 50+ employees whose names I add to my "tracking" worksheet and I want
to quantify all of my data on my "analysis" sheet, so I'm getting data from
another sheet - don't know if this matters. I tried this:
=SUMPRODUCT(--('2007_Corrective'!C:C="LAST,
FIRST"),--('2007_Corrective'!F:F="yes"))
... and I get #NUM! error.

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A100="Joe"),--(B1:B100="yes"))

In article ,
Richard wrote:

I want to count # of times a value(name) appears in a column IF, in
another
column on the same row, "yes" exists. Example:
A1 A6
Joe yes
Joe no
Joe yes

Count here should equal 2.
Thanks