Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum From Variable Range (2000)

    Hello all,

    I am working on a quality reporting project and I am trying to determine the total end-to-end processing time for all manual transactions, i.e. from the time they entered our system to the time they posted and left our system.

    The basic structure of the spreadsheet is (I've attached a sample):

    Column A: TRN

  2. #2
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum From Variable Range (2000)

    Legare's formula is fantastic but I think it needs to have some absolutes used or you may run into a few problems.

    =IF(E2="FEDIN1",MAX(($A$2:$A$26=A2)*$G$2:$G$26)-G2,"")

    just an observation.

    yoyo

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

    Re: Sum From Variable Range (2000)

    Put the formula below into cell L2. It is an array formula, so you must hold down Shift+Ctrl when you press Enter to enter the formula into the cell. Then copy the formula down the column.

    <pre>=IF(E2="FEDIN1",MAX((A2:A26=A2)*G2:G26)-G2,"")
    </pre>


    You will need to adjust the end of the ranges to fit your actual worksheet.
    Legare Coleman

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

    Re: Sum From Variable Range (2000)

    You are correct, it would have been better to make those ranges absolute. It actually should work just fine without the absolutes addresses, but if the worksheet layout were changed, it could break. Thanks for the improvement.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum From Variable Range (2000)

    A big thanks to both Legare and yoyo - I tried the formula this morning and it worked perfectly!

    Scott

Posting Permissions

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