vlookup, match, index: all some or one?
 

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!

vlookup, match, index: all some or one?
 

=sumproduct(--(a2:a100="Salesperson 3"),--(b2:b100="Event 6"))

"dj479794" wrote:

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!

vlookup, match, index: all some or one?
 

Something like this would work:

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


"dj479794" wrote:

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!

vlookup, match, index: all some or one?
 

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?

"bpeltzer" wrote:

=sumproduct(--(a2:a100="Salesperson 3"),--(b2:b100="Event 6"))

"dj479794" wrote:

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!

vlookup, match, index: all some or one?
 

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?

"Vergel Adriano" wrote:

Something like this would work:

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


"dj479794" wrote:

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!

vlookup, match, index: all some or one?
 

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".


"dj479794" wrote:

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?

"Vergel Adriano" wrote:

Something like this would work:

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


"dj479794" wrote:

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!