Excel

[calof1]

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,

[#2]

Hi
It sounds like you need to use a SUMIFS formula

= SUMIFS ( Column to Sum., Column1, Criteria1, Column2, Criteria2 etc)

It's not clear from the attached file what you are trying to achieve sorry so I haven't added an example

[#3]

I suggest you manually enter your desired results and then explain how you arrived at those results so that the relevant formula to correctly return them may be reasonably deciphered.

[#4]

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

[#5]

@calof1 

Take a look at the attached to see if it's what you need

[#6]

Hi@Wyn Hopkins 

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,

[#7]

@calof1 

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))

[#8 · Accepted solution]

@calof1 

My solution attached

[#9]

Thank you, much appreciated@Twifoo 

[#10]

Thank you, much appreciated@Wyn Hopkins