Need help with formula involving lookups, indexes, matches -- not sure how to accomplish this

[CaraM]

I'm

sorry that I'm posting this in a rush so this may not be the best way to illustrate my problem - but here's an idea of what I want and how I'm getting it now:​

	A	B	C	D	E	F	G	H	I	J
1	Fund	Value	Large Stock	Mid Stock	Small Stock	Intl Stock	ST Bonds	Mid Bonds	LT Bonds	Cash
2	Fund A	10000							100%
3	Fund B	5000		60%	40%
4	Fund C	75000		100%

<tbody>
</tbody>

This shows the funds I hold and their individual allocations (Fund B consists of investments that are 60% mid-sized stocks and 40% small stocks, and I have $5,000 in Fund B)

In a separate sheet, I want to TOTAL the values of ALL of my holdings BY Asset Class.

e.g.,

Large Stock	$0
Mid Stock	$10,500
Small Stock	$2,000
Intl Stock	$0
ST Bonds	$0
Int Bond	$0
LT Bonds	$10,000
Cash	$0

<tbody>
</tbody>

In the past, I used a series of formulas like =($B$2*C2)+($B$3*C3)+($B$4*C4), but I'm hoping to do this more elegantly by LOOKING UP the asset class in my "holdings" table (the top table) and summing/multiplying for totals in the 2nd sheet.

I can't quite get there on my own.

Thanks in advance!

 

[WassimN]

Hey CaraM

Try the SUMIFS() function. The SUMIFS() function will sum the values in Column B if the set of criteria match, so if you want to look for Large Stock then the SUMIFS() will be something like Add all the Rows in Column B that have "Large Stock" in Column C. And the SUMIF() function will work if you only have one criteria to use.

Hope this helps.

 

[CaraM]

Thanks for trying to help. I was getting frustrated because I knew this should be do-able, so I kept digging and came up with

=SUMPRODUCT(Holdings[Value],INDIRECT("Holdings["&A3&"]"))/SUM(Holdings[Value])​

after making my holdings data into a TABLE.

I added the "/SUM(Holdings[Value]) because I really wanted to see the class %, not total. Seems to be working!