Averages and Max values if certain criteria are selected

[mniedert]

I need some help with a series of related formulas. I'm keeping track of some survey feedback forms and need some way to quickly look at averages and max scores over time. The problem is I get lost when there are multiple variables that I need to consider. I know that there are some very smart people out there that can solve this in no time. I appreciate your willingness to help me. Here is a sample of what I'm trying to do (re: Report tab) along with a sample of the data.

Here's the data sheet:

Excel 2012
	A	B	C	D	E	F	G	H
1	Category	Supplier	Date of Review	Sales	Product	Service	Cost	Overall
2	Meats	Deli Meat Express	6/18/2015	2.75	2.72	2.88	3.13	2.87
3	Meats	Roscoe Pig Farm	5/23/2015	2.24	1.97	1.89	3.52	2.41
4	Landscaping Suppliers	Lenny Farms	9/11/2014	2.74	2.75	2.71	2.88	2.77
5	Landscaping Suppliers	Silver Star Trees	8/28/2014	2.85	3.04	3.10	2.63	2.91
6	Meats	Deli Meat Express	8/21/2014	2.71	3.11	3.10	2.35	2.82
7	Logisitcs	Sam Trade Inc	8/21/2013	2.71	3.11	3.10	2.35	2.82
8	Construction	DLRC, Inc.	8/15/2013	2.82	3.13	3.30	3.00	3.06
9	Gift Suppliers	Notion's Potions	8/7/2013	2.25	2.22	2.83	2.50	2.45
10	Gift Suppliers	Deli Meat Express	3/5/2013	2.37	2.17	2.83	2.50	2.47
11	Logisitcs	Hugh Packing Co	3/5/2013	2.25	2.24	2.17	2.33	2.25
12	Meats	Roscoe Pig Farm	8/23/2012	2.24	1.97	1.89	2.00	2.03
13	Logisitcs	DLRC, Inc.	8/15/2012	2.72	2.75	2.83	2.42	2.68
14	Meats	Deli Meat Express	8/7/2012	2.25	2.22	2.83	2.50	2.45
15
16		*suppliers can be associated with multiple categories
Data

and here is the sheet that I need the formulas for (specifically those cells that are highlighed):

Excel 2012
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Select Supplier:	Deli Meat Express	<- this is a validation list that I've already created
2	Select Category:	Meats	<- this should be a validation list that only shows the categories that the vendor is associated with on the Data sheet
3	Look Back (months):	24	<- I want the scores below to be limited to the scores within the last x number of months
4
5
6			Sales	Product	Service	Cost	Overall
7		Deli Meat Express - average over 24 months	2.73	2.92	2.99	2.74	2.84	<-should look at Supplier and Look Back to calaculate
8		Deli Meat Express - Best Score over 24 months	2.75	3.11	3.10	3.13	2.87	<-should look at Supplier and Look Back to calaculate
9		Best score in the last 24 months in the Meats category	2.75	3.11	3.10	3.52	2.87	<- should look at the category and months Look Back to calculate
10		Best score in the last 24 months across all categories	2.85	3.13	3.30	3.52	3.06	<- should look at the months Look Back to calculate
11
12			*The numbers above are a representation of the results I'm looking for. They'd obviously change as I changed the criteria in the top left (B1, B2, and B3)
13
Reports

In B2 I need a formula that would show only the categories that the supplier selected in B1 has been associated with on the Data tab. The text in blue italics is for your reference only. So what formulas do I need?

Thanks!

 

[rilzniak]

B2 will need to be Data Validation. This link should help: Excel Dependent Drop-down Lists - Easy Excel Tutorial

C7: =AVERAGEIFS(Data!$D$2:$D$14,Data!$A$2:$A$14,$B$2,Data!$B$2:$B$14,$B$1,Data!$C$2:$C$14,">"&EDATE(TODAY(),-24))

C8: ={MAX((Data!$B$2:$B$14=$B$1)*(Data!$C$2:$C$14>EDATE(TODAY(),-24)),Data!$D$2:$D$14)}

C9: ={MAX((Data!$A$2:$A$14=$B$2)*(Data!$C$2:$C$14>EDATE(TODAY(),-24)),Data!$D$2:$D$14)}

C10: ={MAX((Data!$C$2:$C$14>EDATE(TODAY(),-24)),Data!$D$2:$D$14)}

All formulas can be dragged across the columns to get Product, Service, etc. Not sure if your data will be adding rows but if it will the formulas should be altered to become dynamic. Here's what it would look like:

C7: =AVERAGEIFS(INDIRECT("Data!$D$2:$D$"&COUNTA(Data!$A:$A)),INDIRECT("Data!$A$2:$A$"&COUNTA(Data!$A:$A)),$B$2,INDIRECT("Data!$B$2:$B$"&COUNTA(Data!$A:$A)),$B$1,INDIRECT("Data!$C$2:$C$"&COUNTA(Data!$A:$A)),">"&EDATE(TODAY(),-24))

C8: ={MAX((INDIRECT("Data!$B$2:$B$"&COUNTA(Data!$A:$A))=$B$1)*(INDIRECT("Data!$C$2:$C$"&COUNTA(Data!$A:$A))>EDATE(TODAY(),-24)),INDIRECT("Data!$D$2:$D$"&COUNTA(Data!$A:$A)))}

C9: ={MAX((INDIRECT("Data!$A$2:$A$"&COUNTA(Data!$A:$A))=$B$2)*(INDIRECT("Data!$C$2:$C$"&COUNTA(Data!$A:$A))>EDATE(TODAY(),-24)),INDIRECT("Data!$D$2:$D$"&COUNTA(Data!$A:$A)))}

C10: ={MAX((INDIRECT("Data!$C$2:$C$"&COUNTA(Data!$A:$A))>EDATE(TODAY(),-24)),INDIRECT("Data!$D$2:$D$"&COUNTA(Data!$A:$A)))}

I haven't tested these in Excel but I believe they will work. The curly brackets mean you must enter the formula use Ctrl+Shift+Enter rather than just hitting 'Enter'. Good luck!