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:​

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Fund
[/TD]
[TD]Value
[/TD]
[TD]Large Stock
[/TD]
[TD]Mid Stock
[/TD]
[TD]Small Stock[/TD]
[TD]Intl Stock
[/TD]
[TD]ST Bonds
[/TD]
[TD]Mid Bonds
[/TD]
[TD]LT Bonds
[/TD]
[TD]Cash
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Fund A
[/TD]
[TD]10000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Fund B
[/TD]
[TD]5000
[/TD]
[TD][/TD]
[TD]60%
[/TD]
[TD]40%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Fund C
[/TD]
[TD]75000
[/TD]
[TD][/TD]
[TD]100%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.,
[TABLE="width: 250"]
<tbody>[TR]
[TD]Large Stock
[/TD]
[TD]$0
[/TD]
[/TR]
[TR]
[TD]Mid Stock
[/TD]
[TD]$10,500
[/TD]
[/TR]
[TR]
[TD]Small Stock
[/TD]
[TD]$2,000
[/TD]
[/TR]
[TR]
[TD]Intl Stock
[/TD]
[TD]$0
[/TD]
[/TR]
[TR]
[TD]ST Bonds
[/TD]
[TD]$0
[/TD]
[/TR]
[TR]
[TD]Int Bond
[/TD]
[TD]$0
[/TD]
[/TR]
[TR]
[TD]LT Bonds
[/TD]
[TD]$10,000
[/TD]
[/TR]
[TR]
[TD]Cash
[/TD]
[TD]$0
[/TD]
[/TR]
</tbody>[/TABLE]

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!