COUNTIF

[Pinda]

I have a table with 2 columns, 1 column contains either G,
A, or Rs in them, and the other column has names.

I have a formula that counts all the Gs, As and Rs in the
column:-

=COUNTIF($H$8:$H$88,"G")
=COUNTIF($H$8:$H$88,"A")
=COUNTIF($H$8:$H$88,"R")

I want another formula that uses the names as a condition
as well.

For example, count all Gs that Joe Bloggs has. (with Joe
Bloggs being one of the possible names in the name column.


Any ideas? Help would be much appreicated.

Regards,

Bhupinder Rayat

 

[Peo Sjoblom]

Try

=SUMPRODUCT((Name_Range="Joe Bloggs")*(H8:H88="G"))

for better usability replace the criteria with a cell reference where you
type the criteria,
that way you don't have to edit the formula if you change criteria

 

[Pinda]

Peo,

I tried it but it just returns 0. Even if I replace the
criteria with a cell ref I get 0 again. I would have
thought excel cannot multiply phrases such as "G"?

Any ideas?

Regards

 

[Paul]

What Peo gave you is perfectly correct. It will only count where the text is
EXACTLY what you specify. For example, "Joe Bloggs" is not the same as "Joe
Bloggs" (with two spaces) or "Joe Bloggs " (with a space at the end. Try
overtyping the data in one row (or inserting an extra row that you can later
delete) with exactly the text you specify. Also, check that your name and
G/A/R ranges are exactly the same length and cover the same rows - such as
C8:C88 and H8:H88.

 

[Pinda]

Paul & Peo,

Yep thanks that works great.

Cheers guys.

Pinda