How to use Countif and Vlookup togther

Question

I am trying to combine COUNTIF and VLOOKUP function so that I can use it to formulate my work and increase my efficiency.for better understanding suppose a column A has 4 people name like 1.jay 2.harry 3.ray . So i have assign them randomly "0" and "1" . SO ray might get multiple "0" and "1" and these can happen with everyone. So I want to know how may "0" and "1" each one get.

I have tried everything I know but its giving me wrong data

=COUNTIF(VLOOKUP(A2,A:B,2,0),"0")

after using the above code its giving me wrong output .

Answer 1

Method 1

Using the following SUMPRODUCT formula suppose you have named values in Column A as NameList and named values in Column B as Value:

=SUMPRODUCT((NameList=$D2)*(Value=E$1))

If you want to use formula, I would recommend the COUNTIFS function suggested by Scott Craner as it is faster than SUMPRODUCT in a large scale.

Method 2

You can quickly insert a pivot table and set up as shown above to get the count per value per name with a few clicks. Pro is that you do not even need to find the names from the list first, Con is that it is not that flexible to do further calculations from the values within a pivot table.

Answer 2

use COUNTIFS()

=COUNTIFS(A:A,$D2,B:B,E$1)