• 393K Members
• 3,253 Online
• 424K Conversations
SOLVED

Contributor

# Excel

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.

Kind regards,

9 Replies

# Re: Excel

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

# Re: Excel

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.

# Re: Excel

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

# Re: Excel

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

# Re: Excel

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,

# Re: Excel

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

# Re: Excel

My solution attached

# Re: Excel

Thank you, much appreciated@Twifoo

# Re: Excel

Thank you, much appreciated@Wyn Hopkins