SUM of averages

[XcelLearner]

Hi,

I have a List 1 consisting of n tickers, List 1 can change every now and then. I have a database in array A:C, which has m rows, each with several data points (indicator 1, and 2). Database A:C is used to feeds data for tickers in List 1.

How can I build a formula that uses the tickers in List 1 (in the example in the photo, it has 3 tickers AA, AC, and AD, but it can has a lot more) to look for their respective values in array A:C, and calculate the sum of averages of all tickers in the list? In this photo, I manually do the SUM(AVERAGES) for AA, AC, AD to demonstrate the result.

Thank you very much in advance.

 

[StephenCrump]

I think this is what you're trying to do:

	A	B	C	D	E	F	G	H
1
2	AA	2	10		List1:	AA	AC	AD
3	AB	4	6
4	AC	4	2		Result	12.5
5	AD	5	2		Check:	12.5
6	AE	8	4
7
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUM(FILTER(B2:C6,ISNUMBER(MATCH(A2:A6,List1,)))/COLUMNS(B2:C2))
F5	F5	=SUM(AVERAGE(B2:C2),AVERAGE(B4:C4),AVERAGE(B5:C5))

Named Ranges
Name	Refers To	Cells
List1	=Sheet12!$F$2:$H$2	F4

But you don't appear confident above, and in your previous post: SUM of n AVERAGES
about exactly which Average or Sum you need.

We can easily tweak the formula if 12.5 isn't the result you're expecting.