Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Gain/Loss summing (Excel 97)

    I have a database of client fund investments showing name, date, shares, amounts purchased and reinvested dividends. Now, I want to construct a report which, as of a date, calcs the Long and Short term shares and dollar totals for each fund. So Col A would have the Name, B might have S/T shares, C S/T cost, D L/T shares and E L/T cost. At that point, it will be easy to estimate gains and losses in the portfolio by simply creating some formulas based on current market. Add up all the individual lines and you have the portfolio position on that day.
    I have searched on conditional sums and sumif, and am working on the formulas. Right now, I have created for each record a LT or ST indicator in a new column and am attempting to incorporate it in a SUMIF formula. But I do not think I can create a SUMIF using both the Name in Column A and the ST/LT indicator to add up the Shares and Cost in LT and ST amounts. I hesitate to get involved with arrays (they don't recalc automatically, right?) and I don't want to constantly slice-and-dice the database to grind out these totals. I'd like the thing to re-calculate itself , if at all possible.
    So any threads, suggestions or directions I should go would be appreciated. I would think there are some templates out there which I will attempt to locate. But it's fun to work out a solution. Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Gain/Loss summing (Excel 97)

    In the first place, array formulas *do* recalculate automatically.

    If you have more than one condition, it is better to use SUM instead of SUMIF. For example

    =SUM((A1:A10="IBM")*(B1:B10="ST")*(C1:C10))

    entered as an array formula (confirm with Shift+Ctrl+Enter) will sum only those cells in C1:C10 for which the corresponding cell in column A has value IBM and the corresponding cell in column B has value ST.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Gain/Loss summing (Excel 97)

    Thanks, HansV. Glad to find out those arrays do recalculate. I was uncertain due to the special way they get entered. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •