Using vlookup with countif

[Farhan Noor]

In sheet 1, column A - I have a column listed with some name, suppose
Bill
Jordan
James

In sheet 2, column A - I have all the names of sheet 1 column A multiple times.
In sheet 2 column B - there are two results "yes" and "No" for each names. Suppose,
Bill ------------------yes
Jordan -------------No
James--------------No
Bill--------------------yes
James--------------yes
Bill--------------------yes
Bill--------------------No

Now, in sheet 1, column B - I want to put how many times each name got "yes" in sheet 2, column B. Suppose,
Bill ------------- 3
James---------1

I think for this I have to do vlookup and countif together but don't know how to do. I would be very much pleased if anyone help me do this.
Thanks in advance.

 

[philwojo]

You can just do this with a CountIfs formula, try this:

=COUNTIFS(Sheet2!A1:A20,A1,Sheet2!B1:B20,"yes")

Change the ranges for A1:A20 and B1:B20 to be whatever your specific ranges are on Sheet-2. Put the formula in sheet1 column-B and then copy down, it will look at the name from sheet1 and then look at sheet-2 for a matching name and the word "yes".

 

[PCL]

Assuming Sheet1 column "A" is the list of name
B1 = =SUMPRODUCT((Sheet2!$B$2:$B$8="yes")*(Sheet2!$A$2:$A$8=Sheet1!$A1))
Copy and paste down

Do you know that a Pivot Table could do the job