MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help with sum arrays


Posted by Joseph G. on May 24, 2001 7:05 AM

Okay...I asked about this yesterday and have been tinkering with it but no matter what I do I cannot make this damn thing work. So I am going to include as much information as I can so that hopefully someone will be able to assist me.

The Book I am working with contains 2 sheets. One is a data sheet the other is a summary sheet. The summary sheet is designed to display financial information classified by industrial sector for the securities listed on the data sheet.

Right now the firm is invested in ten industry sectors

Basic Materials
Communications
Consumer, Cyclical
Consumer, Non-cyclical
Energy
Financial
Funds
Industrial
Technology
Utilities

Each of these sectors has its own value on the summary sheet. And each of these values is broken up by how much money is invested long, and short.

Here is an example of the formula I am using to display that:

=SUM(IF(Master!AR3:AR500="basic materials",IF(Master!AU3:AU500="L",Master!G3:G500)))

All I have to do is change the "basic materials" to another sector and the amount will change...and all I have to do is change the L to S and it will change to Short purchases...this all works fine (FYI I am using cell values as opposed to cell names because it is quite likley that many other people besides me will need to add more to this book and)

The problem occurs when dealing with the "other" category

what I want to do is create a function that basically tells excel to look at the column in the data sheet for any other sector OTHER than the 10 that are predefined and if it finds one to calculate the value and add it to the "other" value on the summary sheet

Any help would be greatly appreciated

-Joseph G.


Posted by Aladin Akyurek on May 24, 2001 7:36 AM

Joseph,

Just to see whether what follows is what you have in mind.

I'll assume the data below.

{"x","L",5;"Funds","S",7;"y","L",8;"x","S",9;"Industrial","L",10}

They occupy the range A3:C7.

I named the range A3:A7 CATEGORIES, the range B3:B7 FLAGS, and the range C3:C7 VALUES (just as an example).

The following array-formula

=SUM((NOT(ISNUMBER(MATCH(CATEGORIES,{"Basic Materials";"Communications";"Consumer, Cyclical";"Energy";"Financial";"Funds";"Industrial";"Technology";"Utilities"},0)))*(FLAGS="L")*(VALUES)))

results in 13.

Is this what you want?

Aladin

Posted by Joseph G. on May 24, 2001 7:46 AM

This worked perfectly, thank you very much

-Joseph