Average multiple criteria and ranges

[angel87]

Hello, Im trying to write a formula that calculates an average for a location under a specific period.
I have to specify the start and end dates and also the location and then get the average.
With one location I was able to calculate the average using averageifs, but if there are multiple locations I don't know how to make it go calculate the average on the correct column. The data set is a few hundred columns long, so when I specify the location I need the formula to use it to refer to the correct column in the spreadsheet.
For example, the formula should return the following 2 results for the dataset at the below.
[TABLE="width: 286"]
<tbody>[TR]
[TD]Start
[/TD]
[TD][/TD]
[TD]End
[/TD]
[TD][/TD]
[TD]Location
[/TD]
[TD="align: right"]Average
[/TD]
[/TR]
[TR]
[TD]2/1/2018
[/TD]
[TD][/TD]
[TD]5/31/2018
[/TD]
[TD][/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]4.25
[/TD]
[/TR]
[TR]
[TD]8/1/2018
[/TD]
[TD][/TD]
[TD]11/30/2018
[/TD]
[TD][/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 388"]
<tbody>[TR]
[TD] Date
[/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1/1/2017
[/TD]
[TD][/TD]
[TD]5.00
[/TD]
[TD]2.00
[/TD]
[TD]6.00
[/TD]
[TD]6.00
[/TD]
[TD]6.00
[/TD]
[/TR]
[TR]
[TD]2/1/2017
[/TD]
[TD][/TD]
[TD]8.00
[/TD]
[TD]7.00
[/TD]
[TD]4.00
[/TD]
[TD]1.00
[/TD]
[TD]5.00
[/TD]
[/TR]
[TR]
[TD]3/1/2017
[/TD]
[TD][/TD]
[TD]5.00
[/TD]
[TD]9.00
[/TD]
[TD]8.00
[/TD]
[TD]9.00
[/TD]
[TD]6.00
[/TD]
[/TR]
[TR]
[TD]4/1/2017
[/TD]
[TD][/TD]
[TD]0.00
[/TD]
[TD]1.00
[/TD]
[TD]1.00
[/TD]
[TD]4.00
[/TD]
[TD]4.00
[/TD]
[/TR]
[TR]
[TD]5/1/2017
[/TD]
[TD][/TD]
[TD]9.00
[/TD]
[TD]8.00
[/TD]
[TD]4.00
[/TD]
[TD]4.00
[/TD]
[TD]4.00
[/TD]
[/TR]
[TR]
[TD]6/1/2017
[/TD]
[TD][/TD]
[TD]8.00
[/TD]
[TD]7.00
[/TD]
[TD]4.00
[/TD]
[TD]6.00
[/TD]
[TD]6.00
[/TD]
[/TR]
[TR]
[TD]7/1/2017
[/TD]
[TD][/TD]
[TD]1.00
[/TD]
[TD]7.00
[/TD]
[TD]1.00
[/TD]
[TD]0.00
[/TD]
[TD]7.00
[/TD]
[/TR]
[TR]
[TD]8/1/2017
[/TD]
[TD][/TD]
[TD]7.00
[/TD]
[TD]4.00
[/TD]
[TD]6.00
[/TD]
[TD]8.00
[/TD]
[TD]3.00
[/TD]
[/TR]
[TR]
[TD]9/1/2017
[/TD]
[TD][/TD]
[TD]5.00
[/TD]
[TD]3.00
[/TD]
[TD]6.00
[/TD]
[TD]4.00
[/TD]
[TD]0.00
[/TD]
[/TR]
[TR]
[TD]10/1/2017
[/TD]
[TD][/TD]
[TD]4.00
[/TD]
[TD]0.00
[/TD]
[TD]2.00
[/TD]
[TD]4.00
[/TD]
[TD]2.00
[/TD]
[/TR]
[TR]
[TD]11/1/2017
[/TD]
[TD][/TD]
[TD]6.00
[/TD]
[TD]6.00
[/TD]
[TD]3.00
[/TD]
[TD]8.00
[/TD]
[TD]9.00
[/TD]
[/TR]
[TR]
[TD]12/1/2017
[/TD]
[TD][/TD]
[TD]8.00
[/TD]
[TD]8.00
[/TD]
[TD]0.00
[/TD]
[TD]9.00
[/TD]
[TD]6.00
[/TD]
[/TR]
</tbody>[/TABLE]

 

[angel87]

Sorry, just realized I have an issue with the years is used the example. The dates in the two examples should be in 2017.
Idk how to edit original post

 

[inactiveuserps07]

@angel87, if you could provide some actual ranges for us to work with, someone will definitely be able to help you. For instance, which range would the headers of your criteria (i.e., Start, End, Location, Average) be occupying (e.g., H1:K1, etc.)? Which range would the main headers (i.e., data, A, B, C, D, E) be in (e.g., A1:F1, etc.)?

 

[AhoyNC]

See if this works for you.

Excel Workbook
	A	B	C	D	E	F	G
1	Start		End		Location	Average
2	2/1/2017		5/31/2017		C	4.25
3	8/1/2017		11/30/2017		D	6
4
5	Date		A	B	C	D	E
6	1/1/2017		5	2	6	6	6
7	2/1/2017		8	7	4	1	5
8	3/1/2017		5	9	8	9	6
9	4/1/2017		0	1	1	4	4
10	5/1/2017		9	8	4	4	4
11	6/1/2017		8	7	4	6	6
12	7/1/2017		1	7	1	0	7
13	8/1/2017		7	4	6	8	3
14	9/1/2017		5	3	6	4	0
15	10/1/2017		4	0	2	4	2
16	11/1/2017		6	6	3	8	9
17	12/1/2017		8	8	0	9	6
Sheet

 

[angel87]

THANKS AhoyNY!!!!! this is perfect
I always have problems with Index-Match combo, it is so useful...

Thanks again!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.