Results 1 to 7 of 7

20081015, 17:06 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,561
 Thanks
 43
 Thanked 73 Times in 69 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.

20081015, 18:22 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20081015, 18:23 #3
 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  )

20081015, 18:49 #4
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,561
 Thanks
 43
 Thanked 73 Times in 69 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.

20081015, 18:50 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,561
 Thanks
 43
 Thanked 73 Times in 69 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.

20081015, 19:16 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20081015, 19:31 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,561
 Thanks
 43
 Thanked 73 Times in 69 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.