conditional COUNTIF

[Guest]

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

 

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[T. Valko]

Try this:

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

Biff

 

[Guest]

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.

 

[T. Valko]

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

 

[Guest]

Thanks! That answered the question.

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

 

[JE McGimpsey]

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