AverageIFS and header

[BlackMagic]

Hello I am trying to average a list of Pricing of a particular product and a specified date. I know how to get the dates but I without each cell referencing the product (just the top row) I can't figure out how to pull the averages.

	Blue Shirt	Red Shirt	Grey Shirt	Black Shirt
Date	LCBR	LCBS	LCBT	LCBU
9/1/2014	200	260	250	360
9/8/2014	250	245	260	355
9/15/2014	225	230	255	350
9/22/2014	230	250	300	330

<tbody>
</tbody>

Taking this I want to average off of the Product code EX: LCBR and then the date range I'm seeking as well. This data goes back about 10 years I only put a portion of it here to give an example. Please help!

 

[Domenic]

Let's assume that A1:E6 contains the table, let G2 contain the start date, H2 contain the end date, and I2 contain the product code, such as LCBR. Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF($A$3:$A$6>=G2,IF($A$3:$A$6<=H2,INDEX($B$3:$E$6,0,MATCH(I2,$B$2:$E$2,0)))))

Hope this helps!

 

[Marcelo Branco]

According Domenic's setup, if you are using Excel 2007 or a more recent version you can also try

=AVERAGEIFS(INDEX($B$3:$E$6,0,MATCH(I2,$B$2:$E$2,0)),$A$3:$A$6,">="&G2,$A$3:$A$6,"<="&H2)

M.

 

[BlackMagic]

According Domenic's setup, if you are using Excel 2007 or a more recent version you can also try

=AVERAGEIFS(INDEX($B$3:$E$6,0,MATCH(I2,$B$2:$E$2,0)),$A$3:$A$6,">="&G2,$A$3:$A$6,"<="&H2)

M.

SCORE! That rocks!!! So basically we indexed the product code matching it with the exact location and then averaged it if it's in between those dates! Life saver my friend!

 

[Marcelo Branco]

SCORE! That rocks!!! So basically we indexed the product code matching it with the exact location and then averaged it if it's in between those dates! Life saver my friend!

Thanks for the feedback. Glad for helping.

M.