# Thread: sum data based on quarters (2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

7. ## 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
•