I have a summary table of agents that references monthly data that is filterable. I need an average formula for each agent in the summary table that would show me the average of the data dependent upon how the reference data is filtered & if data doesn’t appear for an agent, because they’re filtered out, it would show “n/a” when not.
Below I’ve shown the average, per agent of the unfiltered data in B8:B16.
If filtered to only show Billy Bob in Jan & Feb … Billy Bob would show an average of 7 & John Boy & Mary Sue would show “n/a”.
If filtered to only show Jan & Feb … Billy Bob would be 7
John Boy would be 9
Mary Sue would be 11
A B C
1 Agent Average
2 Billy Bob 12
3 John Boy 14
4 Mary Sue 16
5
6
7 Name Range Month Sum Range
8 Billy Bob Jan 2
9 John Boy Jan 4
10 Mary Sue Jan 6
11 Billy Bob Feb 12
12 John Boy Feb 14
13 Mary Sue Feb 16
14 Billy Bob Mar 22
15 John Boy Mar 24
16 Mary Sue Mar 26
Bookmarks