sumif to aggregate month to quarter

R

renegade

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
 
S

Stefi

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:
 
R

renegade

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!
 
R

renegade

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!
 
S

Stefi

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!





- 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
 
R

renegade

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
 
R

renegade

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!
 
S

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!






- 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
 
R

renegade

Thanks; my system doesn't seem to be the US setting. Could you kindly tell me
where to change the setting? Will this affect my other calculations, since
the users will be UK based?
 
D

David Biddulph

Windows Regional Settings are in the Control Panel.

If you have different settings from those expected in the DATEVALUE input,
you'll need to rearrange the inputs to that function. It is better to use a
function like DATE, which has unambiguous inputs, and it is better always to
use unambiguous date formats.
 
R

renegade

Thank you for this. Do you think there is a relatively straightforward
solution to automatically sum up 3 monthly data into one quarterly data in a
separate sheet? So 01-01, 02-01 and 03-01 can be automatically recognized to
feed into 1Q-01? Thanks!
 
D

David Biddulph

It depends what you mean by relatively straightforward, but the simple
answer is No. You've had some pretty good suggestions already, so read and
understand those formulae and modify them to suit your needs.
 
S

Stefi

It depends what you mean by relatively straightforward, but the simple
answer is No.  You've had some pretty good suggestions already, so readand
understand those formulae and modify them to suit your needs.
--
David Biddulph







- Idézett szöveg megjelenítése -

This is a version not depending on Regional settings:

=SUM(OFFSET(mthly!$A2,0,MATCH(DATE(IF(VALUE(RIGHT(D$1,2))<30,"20","19")
&RIGHT(D$1,2),LEFT(D$1)*3-2,1),mthly!$1:$1,0)-1,1,3))

Regards,
Stefi
 
R

renegade

yes this worked! thank you!

Stefi said:
This is a version not depending on Regional settings:

=SUM(OFFSET(mthly!$A2,0,MATCH(DATE(IF(VALUE(RIGHT(D$1,2))<30,"20","19")
&RIGHT(D$1,2),LEFT(D$1)*3-2,1),mthly!$1:$1,0)-1,1,3))

Regards,
Stefi
.
 
S

Stefi

You are welcome! Thanks for the feedback!

Clicking the YES button will be appreciated.


--
Regards!
Stefi



„renegade†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top