Averaging Columns Containing 0s Based On 2 Criterias

I have a worksheet with 2 tables. Table 1 has NBA players, their team, and 14 columns each showing minutes played in each of the last 14 games. Table 2 is a list of injured NBA players and the team they play for.

I'm trying to add 1 more column to table 1. If a player's team does not appear in table 2, it's simply an average of all 14 columns for a given player on that team. If a player does appear in table 2, I'd like to average only the columns where there are 0s for that player and only for players who play on the same team as that player. I know it involves an array formula and the averageifs function but I can't quite figure it out.

See attached for the worksheet. In the attached example to describe what I'm talking about, if team=ATL, columns C to H are averaged because Kyle Korver from ATL appears in table 2. If team=MIA, columns C, D, F, G, and J are averaged because of the same reason.

Do you mean this:

If I make it right this will work only if:

- There is one injured player per team
- There is no duplicated player names in A column.
(If you have duplicated names put in one team first players First name in other first players Last name.)

Originally Posted by zbor

If I make it right this will work only if:

- There is one injured player per team
- There is no duplicated player names in A column.
(If you have duplicated names put in one team first players First name in other first players Last name.)

Yes, I meant exactly that, thank you so much.