Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    sum data based on quarters (2002)

    Been racking my brain and I give up.
    I have data alternating: date, value, date, value, etc. -- (from I2:af2)
    I'm trying to find the total of the values that correspond to quarter 1.
    Then, I need the total of the values that correspond to quarter 2, then 3, then 4.

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

    Re: sum data based on quarters (2002)

    I'd organize the data differently - dates in one column (or row), values in the next column (or row). You can then use a SUM or SUMPRODUCT formula.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum data based on quarters (2002)

    A bit hard without seeing the data... buyt try this.

    Transpose the data so that it becomes a vertical list (not horizontal)
    Insert a row at the top - to give headings of 'date', 'Value'

    As the data is ao ordered, write a simple formula so thet the value in cell a3 is copied to b2 (ie in cell b2 type = a3)
    Copy cells b2 and b3 down the full list. Then copy all column B and then paste back as a value.

    Using autofilter, select all the balnks in column B and delete the rows.

    Now you will have a 'clean' lsit, and can use pviot tabels to summarise the date by quarters. ( I hope - )

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sum data based on quarters (2002)

    I wish I could, Hans, but I inherited the spreadsheet and have to work with it this way.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sum data based on quarters (2002)

    I've been playing around with the MOD function to find the alternating columns, but I just can't get the values that correspond to the quarters.
    OFFSET just came to mind...maybe I need to try that.

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

    Re: sum data based on quarters (2002)

    Let's say that you enter the quarters 1 to 4 in A1:A4.
    In B1 enter the following formula:

    =SUMPRODUCT((MOD(COLUMN($J$2:$AF$2),2)=0)*(INT((MO NTH($I$2:$AE$2)-1)/3+1)=A1)*$J$2:$AF$2)

    and fill down to B4. Alternatively, enter the following array formula (confirm with Ctrl+Shift+Enter):

    =SUM(IF((MOD(COLUMN($J$2:$AF$2),2)=0)*(INT((MONTH( $I$2:$AE$2)-1)/3+1)=A1),$J$2:$AF$2))

    See attached sample workbook.
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: sum data based on quarters (2002)

    Thank you, Hans. I see it WAS an offset but not the OFFSET function. Again, thanks for clearing up my confusion.

Posting Permissions

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