Finding Averages Across Multiple Columns That Meet Certain Criteria

[kiwi1]

Hi,

I want to be able to work out averages from a spreadsheet like below, across multiple columns. So for example, all of the averages from KWB1** to get a value f 2 returned, and then KWB2** to get what would be 7 in this example. Is there a way of just doing that in one formula?

[TABLE="width: 387"]
<tbody>[TR]
[TD="align: right"]KWB1T1[/TD]
[TD="align: right"]KWB1T2[/TD]
[TD="align: right"]KWB1T3[/TD]
[TD="align: right"]KWB2T1[/TD]
[TD="align: right"]KWB2T2[/TD]
[TD="align: right"]KWB2T3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	KWB1T1	KWB1T2	KWB1T3	KWB2T1	KWB2T2	KWB2T3		KWB1	2
2	1	2	3	6	7	8		KWB2	7
3	1	2	3	6	7	8
4	1	2	3	6	7	8
5	1	2	3	6	7	8
Sheet1

In I1 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(LEFT($A$1:$F$1,LEN($H1))=$H1,$A$2:$F$5))

 

[kiwi1]

Much more complicated than I expected it to be, haha. Thank you. Very much appreciated.

 

[Aladin Akyurek]

Much more complicated than I expected it to be, haha. Thank you. Very much appreciated.

You are welcome.