Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing in an Array (97 SR2)

    Hello everyone. I feel embarrased to ask this question, but I am stumped.

    I have a two by fifty array with a two character alpha code in column A and a number in column B representing the number of minutes devoted to the activity represented by the alpha character on the same row in column A.

    In column E, I have a 1x18 array that holds the list of alpha codes found in column A. I am trying to place a formula in column D that will total all of the minutes devoted to each of the 18 task catagories.

    The formula I have now is:

    =SUM((A2:A50=E2)*B2:B50)

    This formula displays a zero. However when I highlight the cell with the formula in it and click on the "paste function" icon from the menu bar, Excel opens a function "detail" window that shows the accurate summation of the time segments. Why can I see the formula working there, but Excel only shows me a zero in the cell holding the formula?

    D.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing in an Array (97 SR2)

    Try:

    <code>
    =SUMIF($A$2:$A$50,E2,$B$2:$B$50)
    </code>
    Legare Coleman

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

    Re: Summing in an Array (97 SR2)

    This formula is an array formula, i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter.

    An alternative "normal" (non-array) formula would be

    =SUMIF(A2:A50,E2,B2:B50)

    or

    =SUMPRODUCT((A2:A50=E2)*B2:B50)

  4. #4
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing in an Array (97 SR2)

    Hey, that did it. Thanks.

    D.

Posting Permissions

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