-1

I have 2 worksheets, what I want to achieve is to count how many times an item in column B appears if column A has a value of "1" on it.

Worksheet 1 Column A (A2=1, A4=1, A7=1, A10=1, A13=1, A14=1, A15=1, A17=1, A18=1

Column B (B1= John Doe, B2= Philip Seymour, B3= Anthony Jones, B4= Thomas Moore, B5= Lindsey Lohan, B6= Rudy Duterte, B7= Philip Seymour, B8= Anthony Jones, B9= John Doe, B10= Philip Seymour, B11= Anthony Jones, B12= Thomas Moore, B13=Lindsey Lohan, B14= Rudy Duterte, B15= Anthony Jones, B16= John Doe, B17= Rudy Duterte

the result will be reflected in worksheet two like this

John Doe = 3 Philip Seymour = 1 Anthony Jones = 1 Thomas Moore = 1 Lindsey Lohan = 1 Rudy Duterte = 3

Improve this question
2
  • do you need all those values in one cell, or is it ok to have them in separate ones? Just to sum up 1-s easiest way is to use a pivot table.
    – Máté Juhász
    Jun 3, 2015 at 7:20
  • The results that you show in your question don't match your description of the problem (and your input data).  Part of the discrepancy might be that your Column A data go from row 2 to row 18, but your Column B data go from row 1 to row 17.
    – Scott - Слава Україні
    Jun 3, 2015 at 15:10

1 Answer 1

Reset to default
0

Found this and got the result I wanted:

=SUMPRODUCT(('2015'!$B$2:$B$19=Count!A2)*('2015'!$A$2:$A$19=1))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .