Count ifs multiple criteria & average ifs multiple criteria

Hi All,

I am trying to solve this scenario.

Column A: Cars (Toyota, Chevy, GMC, etc.)
Column B: Salesperson (John, Lance, Kathy, Kassidy)

Im trying to do a countifs function to get the total amount of any two specific salesman. For example, i want to know how many toyotas both John and lance sold. I tried =COUNTIF(A1:A10, "=Toyota", B1:B10, "=John", A1:A10, "Lance") but it comes up with "0" im guessing because the three criterias are not being met. I know i can do two separate countif functions and add them but i wanted to see if it could be done in one.

Also,

adding to the above statement. Column C: Duration in days (10, 20, 5, etc.) i am trying to also do an averageifs function for the above scenario. For example, on average how long did it take john and lance to sell those toyotas. But i keep getting an error. I am trying =AVERAGEIFS(C1:C10, A1:A10, "=Toyota",B1:B10, "=John", A1:A10, "Lance")

Thank you for the help!

Hi,
Try:
=SUM(((B2:B6="John")+(B2:B6="Lance"))*(A2:A6="Toyota"))

You can use COUNTIFS as TMS did if you want. You get kind of the same result using IF like this though. And it extends to getting the average more easily.

Cars in A2:A4
People in B2:B4
Durations in C2:C4

Car to search (eg Toyota) in F1
People to search (eg John and Lance) in F2:F3

Count (in F5):
=SUM(IF(($A$2:$A$4=$F$1)*($B$2:$B$4=TRANSPOSE($F$2:$F$3)),1,0))

Average:
=SUM(IF(($A$2:$A$4=$F$1)*($B$2:$B$4=TRANSPOSE($F$2:$F$3)),$C$2:$C$4,0))/$F$5

See attached mockup.