Index and Match with Sum having 2 columns

[srinivaspokala]

Hi All,
I need country wise total(Weight %) under each sectors displayed in column heading from list securities. Can you please check and advice the better formula to use, have tried doing with index and match and not getting exact results

Output should be like below with values.

Portfolio absolute weights (excl. cash, derivatives, money market funds) (incl. equity funds)

Consumer Services

Energy

Materials

Capital Goods

Commercial & Professional Services

Transportation

Automobiles & Components

Consumer Durables & Apparel

Hotels Restaurants & Leisure

Media & Entertainment

Retailing

Food & Staples Retailing

Food, Beverage & Tobacco

Household & Personal Products

Health Care Equipment & Services

Pharmaceuticals, Biotechnology & Life Sciences

Banks

Diversified Financials

Insurance

Real Estate

Software & Services

Technology Hardware & Equipment

Semiconductors & Semiconductor Equipment

Telecommunication Services

Utilities

Total

United States

Canada

Other North American Developped (describe)

UK

France

Switzerland

Germany

Data I have is below

Name	Weight %	Sector	Country of Risk
MARVELL TECH GRP LTD	0.284027​	Semiconductors & Semiconductor Equipment	United States
ATLASSIAN CORP PLC	0.251582​	Software & Services	United States
	0.024015​	Unknown	Ireland
CHECK POINT SOFTWARE	0.14749​	Software & Services	Israel
NXP SEMICONDUCTR	0.369721​	Semiconductors & Semiconductor Equipment	Netherlands
ACTIVISION BLIZZARD	0.579235​	Media & Entertainment	United States
ADOBE INC	1.8178​	Software & Services	United States
ADV MICRO DEVICES	0.851615​	Semiconductors & Semiconductor Equipment	United States
ALEXION PHARM INC	0.279837​	Pharmaceuticals, Biotechnology & Life Sciences	United States
ALIGN TECH INC	0.344706​	Health Care Equipment & Services	United States
ALPHABET INC	3.28378​	Media & Entertainment	United States
ALPHABET INC	2.979856​	Media & Entertainment	United States

Thanks

 

[Misca]

Welcome to the board!

INDEX / MATCH would be perfect if you wanted to get the values from your matrix to the table below but I believe you're trying to sum the values from table below to the first table.

I think you could write an INDEX / MATCH formula if you tried hard enough but usually the best solution is the easiest one:

=SUMIFS(Table1[Weight %],Table1[Sector],C$1,Table1[Country of Risk],$A3)

Adjust the ranges to match your data.

If you want to see the average instead of sum use AVERAGEIFS with the same parameters.

 

[srinivaspokala]

Welcome to the board!

INDEX / MATCH would be perfect if you wanted to get the values from your matrix to the table below but I believe you're trying to sum the values from table below to the first table.

I think you could write an INDEX / MATCH formula if you tried hard enough but usually the best solution is the easiest one:

=SUMIFS(Table1[Weight %],Table1[Sector],C$1,Table1[Country of Risk],$A3)

Adjust the ranges to match your data.

If you want to see the average instead of sum use AVERAGEIFS with the same parameters.

Amazing, worked out successfully in first attempt. I don't know that we can do lot with sumif. Thanks a lot for your quick help.