vlookup, match, index: all some or one?

D

dj479794

Situation: I have the following data in cells:

A2: "Salesperson 1"
A3: "Salesperson 3"
A4: "Salesperson 5"
A5: "Salesperson 3"
A6: "Salesperson 9"
A7: "Salesperson 8"
....and so on....

B2: "Event 1"
B3: "Event 3"
B4: "Event 6"
B5: "Event 1"
B6: "Event 9"
.....and so on...


If in Cell D2 I have "Salesperson 3"
And I want to find out a Count Everytime Salesperson 3 has done Event 6.
What formula do I use?

Thanks!
 
V

Vergel Adriano

Something like this would work:

=SUMPRODUCT((A2:A500="Salesperson 3")*(B2:B500="Event 6")*1)
 
D

dj479794

It seems that when I use that it will count Event 6 even though Salespeson 3
is not tied to that event. I dont want to count it unless the specific
salesperson is associated with that event. Any suggestions?
 
D

dj479794

It seems that when I use that it will count Event 6 even though Salespeson 3
is not tied to that event. I dont want to count it unless the specific
salesperson is associated with that event. Any suggestions?
 
V

Vergel Adriano

Did you try it?

It will give you a count of 1 for each row where column A is "Salesperson 3"
and column B is "Event 6".
 
Top