Jun 20 2019 06:04 PM
Hi,
I have different categories such as "Asset Class". They each have a series of assets in each category, such as 5 domestic stocks, 10 international. I want a formula to sum up the total of each category based on asset class. Excel spreadsheet is attached, and data is on the "Fund Manager Attribution" tab.
Please assist.
Kind regards,
Jun 20 2019 08:51 PM
Jun 20 2019 08:51 PM
Jun 20 2019 10:03 PM
Hi @Wyn Hopkins
Thank you for the reply.
The issue with my data is all the relevant values are in different columns, this has meant i have been unable to determine how to a sumif formula. I have attached a file which shows my data, it has the column for each shares returns. As i have large number of stocks, i am looking for a formula that will just for the selected stock between two given dates.
Please let me know any questions.
Thank you kindly for your help.
Calof1
Jun 20 2019 11:44 PM
Thank you for the reply.
The formula will match my needs for date range. As my data is spreadout amongst many columns, do you have an idea on how to get it to select the relevant column to sum up based on the stock code. Eg i set the criteria to SGH, and the formula will read the column for SGH? Essentially i am looking to incorporate the Stock Code being the indentifer for it sum up the relevant column.
Thanks again,
Jun 21 2019 12:15 AM
In the attached file, the formula in H8, copied across to J8, is:
=SUMIFS(INDEX(StockTable,0,MATCH(H7,StockLabels,0)),
INDEX(StockTable,0,1),">="&$H5,
INDEX(StockTable,0,1),"<="&$H6)
Note the following defined names therein:
Name | Formula |
StockLabels | =Sheet1!$A$1:INDEX(Sheet1!$1:$1,COUNTA(Sheet1!$1:$1)) |
StockTable | =Sheet1!$A$2:INDEX(Sheet1!$1:$1048576,1048576,COLUMNS(StockLabels)) |
Jun 21 2019 12:36 AM
Solution