Information from one column then filtered(?) from another

D

Deborah S.

Here is a brief example of what I am trying to accomplish:
A B
1 Don
2 Jim w
3 Don w
4 Bob
5 Jim w

I would like to count the number of times that Don's name is on the list,
then the number of times Don has a "w" in the next column. The COUNTIF
function works for the first one but how do I get the second amount?

The results should be:
Bob 1
Don 2 1
Jim 2 2

I thank any and all in advance for any help that can be given on this.

Deborah
 
M

Max

Assuming the source data is within A1:B20,
and names are listed in D1 down, viz:
Bob
Don
Jim
Click to expand...
etc

Put in say, F1, and copy down:
=SUMPRODUCT(($A$1:$A$20=D1)*($B$1:$B$20="w"))
Adapt the ranges to suit.
Note that sumproduct doesn't accept entire col references, eg: A:A, B:B
 
B

Biff

Hi!

Try this:

Names listed in D1:D3 -

D1 = Bob
D2 = Don
D3 = Jim

Formula in E1:

=COUNTIF(A$1:A$5,D1)

Formula in F1:

=SUMPRODUCT(--(A$1:A$5=D1),--(B$1:B$5="w"))

Select both E1 and F1 and copy down as needed.

Biff
 
D

Deborah S.

Thanks so much, worked great.

Biff said:
Hi!

Try this:

Names listed in D1:D3 -

D1 = Bob
D2 = Don
D3 = Jim

Formula in E1:

=COUNTIF(A$1:A$5,D1)

Formula in F1:

=SUMPRODUCT(--(A$1:A$5=D1),--(B$1:B$5="w"))

Select both E1 and F1 and copy down as needed.

Biff
Click to expand...
 
Top