I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks

If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi



€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks

Stefi

Many thanks for your reply. I want some flexibility, so the "2001" was
simply an example... is there a function which allows you to transfer and
aggregate the data?
say data under Jan-99, Feb-99 and Mar-99 would be automatically included
under 1Q99 in another sheet? Thanks!

"Stefi" wrote:

If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi



€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks

Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet...
thanks!

"Stefi" wrote:

If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi



€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks

On Jan 20, 4:18*pm, renegade
wrote:
Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet....
thanks!



"Stefi" wrote:
If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi

„renegade” ezt írta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 * * * *Feb-01 *Mar-01 *Apr-01 *May-01 *Jun-01 *Jul-01 *Aug-01 *

sheet 2 contains (and continue for many years):
1Q01 *2Q01 * *3Q01

Thanks- Hide quoted text -

- Show quoted text -

Try this modified version:

=SUM(OFFSET(Munka1!$A2,0,(LEFT(A1)*3-3)+MATCH("*"&RIGHT(A1,2),Munka1!
1:1,0)-1,1,3))

Regards,
Stefi

Thanks. But it doesn't seem to work - I guess Jan-99, Feb-99, etc are
recognized as date rather than text. Would you think I could send you the
file by email for you to have a look? Thanks



"Stefi" wrote:

On Jan 20, 4:18 pm, renegade
wrote:
Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet....
thanks!



"Stefi" wrote:
If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi

€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks- Hide quoted text -

- Show quoted text -

Try this modified version:

=SUM(OFFSET(Munka1!$A2,0,(LEFT(A1)*3-3)+MATCH("*"&RIGHT(A1,2),Munka1!
1:1,0)-1,1,3))

Regards,
Stefi
.

I have placed part of the document on the following link:
http://home.drewry.co.uk/pdf/Book1.xml
In Cell D2 of the Qrtly Sheet, is there a formula to aggregate the 3 months
data from the Mthly sheet, which I can copy across the whole row (Row 2 in
Qrtly)? Thank you!

"Stefi" wrote:

On Jan 20, 4:18 pm, renegade
wrote:
Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet....
thanks!



"Stefi" wrote:
If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi

€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks- Hide quoted text -

- Show quoted text -

Try this modified version:

=SUM(OFFSET(Munka1!$A2,0,(LEFT(A1)*3-3)+MATCH("*"&RIGHT(A1,2),Munka1!
1:1,0)-1,1,3))

Regards,
Stefi
.

I have laid out the data in an exmple (follow the link please):
http://home.drewry.co.uk/pdf/Book1.xml

So what formula can be used in D2 in qrtly sheet, to calculate the sum of
the 3 monthly data contained in D2-F2 in the Mthly sheet, and this formula
can be copied across the entire Row 2 of the Qrtly sheet? Thanks!


"Stefi" wrote:

On Jan 20, 4:18 pm, renegade
wrote:
Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet....
thanks!



"Stefi" wrote:
If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi

€žrenegade€� ezt Ã*rta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 Feb-01 Mar-01 Apr-01 May-01 Jun-01 Jul-01 Aug-01

sheet 2 contains (and continue for many years):
1Q01 2Q01 3Q01

Thanks- Hide quoted text -

- Show quoted text -

Try this modified version:

=SUM(OFFSET(Munka1!$A2,0,(LEFT(A1)*3-3)+MATCH("*"&RIGHT(A1,2),Munka1!
1:1,0)-1,1,3))

Regards,
Stefi
.

On Jan 21, 5:51*pm, renegade
wrote:
I have laid out the data in an exmple (follow the link please):http://home.drewry.co.uk/pdf/Book1.xml

So what formula can be used in D2 in qrtly sheet, to calculate the sum of
the 3 monthly data contained in D2-F2 in the Mthly sheet, and this formula
can be copied across the entire Row 2 of the Qrtly sheet? Thanks!



"Stefi" wrote:
On Jan 20, 4:18 pm, renegade
wrote:
Many thanks for your reply. What I need would be some flexibility, so the
"2001" was simply an example... so I would need a function which allows me to
aggregate monthly data from one sheet to qrtly data in another... so Jan,
Feb, and Mar-99 data would be summed up as 1Q-99 data in a separate sheet....
thanks!

"Stefi" wrote:
If data to be summed are in row2, sheet1, then

=SUM(OFFSET(Sheet1!$A2,0,(LEFT(A1)*3-3)+(RIGHT(A1,2)-1)*12,1,3))

provided that years really begin with 01.

--
Regards!
Stefi

„renegade” ezt írta:

I have some monthly data in one sheet which I wish to transfer and aggregate
to another sheet at a quarterly level. I couldn't figure out if sumif
function would work here, or is any other methods? E.g.
sheet 1 contains (and continue for many years):
Jan-01 * * * *Feb-01 *Mar-01 *Apr-01 *May-01 *Jun-01 *Jul-01 *Aug-01 *

sheet 2 contains (and continue for many years):
1Q01 *2Q01 * *3Q01

Thanks- Hide quoted text -

- Show quoted text -

Try this modified version:

=SUM(OFFSET(Munka1!$A2,0,(LEFT(A1)*3-3)+MATCH("*"&RIGHT(A1,2),Munka1!
1:1,0)-1,1,3))

Regards,
Stefi
.- Hide quoted text -

- Show quoted text -

In D2:

=SUM(OFFSET(mthly!$A2,0,MATCH(DATEVALUE(LEFT(D$1)* 3-2&"/1/"&RIGHT(D
$1,2)),mthly!$1:$1,0)-1,1,3))

Watch the DATEVALUE part of the formula, it depends on your Regional
settings. This formula matches US setting, adjust if you have another
one!

Regards,
Stefi

Excel 2007 PivotTable
Consolidate monthly, quarterly data for multiple years.
Quite flexible.
http://c0444202.cdn.cloudfiles.racks.../01_20_10.xlsx