I have large data sheets were I need to calculate the average value only for certain team members performing certain service call types. There are several different teams involved. The results need to be displayed in a interactive dashboard were the user can choose the team to see that teams result.
So I need to build a formula that uses a named range that contains a list of team members. I don't want to use named ranges in the data sheet itself.
The data sheet has one row for each service call.
Colum A has the team member ie: 20TE01, 20TE15, 20TE78 ect ect.
Colum B has the service call type ie:, M, A, S, O, ect, ect.
Colum C has the value I need to average.
Named Range is "teamA" contains 20TE01 and 20TE15 as an example
I can use this to count the call type "M" by team as an example =SUMPRODUCT(COUNTIFS(A:A,teama,B:B,"M"))
What formula will provide me the average of a particular call type by team?
Attached is a basic example of the structure of the data sheet.
Bookmarks