|
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
Excel 2007 PivotTable
Consolidate monthly, quarterly data for multiple years. Quite flexible. http://c0444202.cdn.cloudfiles.racks.../01_20_10.xlsx |
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
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 . |
sumif to aggregate month to quarter
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 . |
sumif to aggregate month to quarter
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 . |
sumif to aggregate month to quarter
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 |
sumif to aggregate month to quarter
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? "Stefi" wrote: 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 . |
sumif to aggregate month to quarter
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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . |
sumif to aggregate month to quarter
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! "David Biddulph" wrote: 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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . . |
sumif to aggregate month to quarter
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. -- David Biddulph "renegade" wrote in message ... 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! "David Biddulph" wrote: 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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . . |
sumif to aggregate month to quarter
On jan. 22, 16:42, "David Biddulph" <groups [at] biddulph.org.uk
wrote: 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. -- David Biddulph "renegade" wrote in message ... 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! "David Biddulph" wrote: 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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . .- Idézett szöveg elrejtése - - 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 |
sumif to aggregate month to quarter
yes this worked! thank you!
"Stefi" wrote: On jan. 22, 16:42, "David Biddulph" <groups [at] biddulph.org.uk wrote: 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. -- David Biddulph "renegade" wrote in message ... 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! "David Biddulph" wrote: 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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . .- IdĂ©zett szöveg elrejtĂ©se - - 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 . |
sumif to aggregate month to quarter
You are welcome! Thanks for the feedback!
Clicking the YES button will be appreciated. -- Regards! Stefi €žrenegade€ť ezt Ă*rta: yes this worked! thank you! "Stefi" wrote: On jan. 22, 16:42, "David Biddulph" <groups [at] biddulph.org.uk wrote: 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. -- David Biddulph "renegade" wrote in message ... 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! "David Biddulph" wrote: 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. -- David Biddulph "renegade" wrote in message ... 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? "Stefi" wrote: 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 . .- IdĂ©zett szöveg elrejtĂ©se - - 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 . |
| All times are GMT +1. The time now is 10:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com