Dropdown lists with Averageifs on a seperate worksheet

[rholdren]

Good morning.
I've been experimenting with dropdown lists in a worksheet and became curious about if I can make data
appear in a totals box based on the dropdown list. I was able to do that with an If statement and one thing
lead to another so I built a worksheet and from it the monster grew.



I began experimenting with SumIfs and AverageIfs and loaded a bunch of test data on the worksheet "Data"
So I asked myself what if I wanted all the data in a range to be averaged based on a certain criteria, not just one set, but
multiple sets of criteria. What if I needed Mickey Mouse averages for January while Donald Duck was his assistant?


Mickey Mouse name appears intermittenly in Colum AG along with several others who are also on the dropdown list on the "Team Totals"
worksheet in Cell A6. The link below got me started but then it curiosity grew from there.


https://www.mrexcel.com/forum/excel...-contains.html?highlight=Averageifs+worksheet


I know I could build a userform to do this based off of what I learned here a little while back but how would I do this and keep
it just on the worksheet?



Worksheet "Team Totals"
Data is in worksheet "Data"
need to averageifs from worksheet "Data"



Average Column D Range D1.D1000 when the 4 qualifications are met (believe it or not I originally had more but thought it was overkill)



Date from Column A Range A1.A1000 >12/31/2017
Date from Column A Range A1.A1000 <2/1/18



persons name from Column AG Range AG1.AG1000 = Mickey Mouse
Assistan name from Column AH Range AH1.AH1000 = Donald Duck


This Data would appear in Cell C2 of "Team Totals"
Keeping in mind that Mickey Mouse is appearing in a dropdown list that is in Cell A6 and Donald in a drop down list A8 of Team Totals"

if the dropdown box changes to Minnie Mouse then the Cell C2 would show the averages for Minnie and if it changed Assistant changed
to Goofy it would show where Goofy so there is many possible comginations. I also have many other names on both lists
Joe Schmoe, Betty Boop, Superman, Batman, Popeye, Olive Oil, Bluto, Whimpy etc.
Any thoughts on this? Just curious.
BTW - I have learned bunches from the board and that is fueling my curiousity. Thanks to the folks here not only is my
curiousity growning but my capabilities as well. Best Place ever for learning, without a doubt comes from what you can
read on the threads in this BB. Real World problems with real world solutions.
Thanks for everything

 

[rholdren]

I soved everything I needed to except for on thing. The formula below is just one of a variety of formulas but they all have one thing in common:
Where Betty Boop is in the formula here se is added into the function using the insert function. However, her name as well as others are part of a dropdown
list named TestCharecters. So, how would I get Donald Duck to replace Betty Boop in the fromula when I change the name in the dropdown list?

TestData!
=SUMIFS(TestData!AL3:AL20000,AG3:AG20000,"Betty Boop",TestData!C3:C20000,">12/31/18",TestData!C3:C20000,"<2/1/19")

Thanks