Using a list of options and countifs

[perducci]

I have a list of sales and sales people (see attached excel file). In my head I know that certain reps only sell a certain product. I just want to count the number of sales made for each rep by date and by product.

For Example:
Scott - Product1
Cory - Product1
Sam - Product2
Ryan - Product1
Jerret - Product2

I don't want to use an equation like:
=COUNTIFS(Table1[REP],"Scott")+COUNTIFS(Table1[REP],"Cory")+COUNTIFS(Table1[REP],"Ryan")
because the real problem is much more complex.

I wish I could say:
=COUNTIFS(Table1[REP],OR(Scott,Cory,Ryan))
but of course this doesn't work.

Is there a way I can use a list of names in a single countifs function or some other function?

Thanks

[NBVC]

Try:

=SUM(COUNTIFS(Table1[REP],{"Scott","Cory","Ryan"}))

If you create a list in a column somewhere of matches to count then use something like:

=SUMPRODUCT(COUNTIFS(Table1[REP],J2:J4))

where J2:J4 contains the shortlist of names

[perducci]

Really cool to finally have a solution, this problem has been recurring in my work and this will speed me up a ton.

Thanks!