|
sumproduct by quarter
On one tab I have the following data:
Month Currency Amount 1 JPY 50.00 3 GBP 75.00 4 JPY 50.00 7 GBP 35.00 On another tab, I've setup the Sumproduct formula to give me the amounts that correspond with the correct currency, but how can I get a total by quarter? For example, on this other tab, I'd like to allow the user to select a month, then have the spreadsheet autopopulate the quarterly info to-date based on the month they selected... So if the user selects the month April and JPY...in the first column it will add together any JPY amount with a month less than or equal to March (3)...and it column two it will add together any amounts with a month of April (4)...and disregard anything beyond that. Any ideas how I can do this? I can get the first QTR column to work with the following formula... =(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000)),SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000))) But can't get this work for the periods 4, 5 & 6. Any ideas? |
sumproduct by quarter
=SUMPRODUCT(--(INT((A2:A99-1)/3)=0),--(B2:B99="jpy"),C2:C99)
That 0 (in the first portion of the formula) represents quarter 1. If you wanted the 4th quarter, then use =3. Brian wrote: On one tab I have the following data: Month Currency Amount 1 JPY 50.00 3 GBP 75.00 4 JPY 50.00 7 GBP 35.00 On another tab, I've setup the Sumproduct formula to give me the amounts that correspond with the correct currency, but how can I get a total by quarter? For example, on this other tab, I'd like to allow the user to select a month, then have the spreadsheet autopopulate the quarterly info to-date based on the month they selected... So if the user selects the month April and JPY...in the first column it will add together any JPY amount with a month less than or equal to March (3)...and it column two it will add together any amounts with a month of April (4)...and disregard anything beyond that. Any ideas how I can do this? I can get the first QTR column to work with the following formula... =(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000)),SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000))) But can't get this work for the periods 4, 5 & 6. Any ideas? -- Dave Peterson |
sumproduct by quarter
Dave,
Thanks...but this doesn't appear to do what I need it to... I want it to add the data in seperate columns depending on what quarter it occured in. If it's in the first qtr...I want it only to add month 1-3...but I want to give the person viewing the report the ability to choose the specific month. For example if the person only selects the month of Feb...I only want it to add together months 1&2...etc.... "Dave Peterson" wrote: =SUMPRODUCT(--(INT((A2:A99-1)/3)=0),--(B2:B99="jpy"),C2:C99) That 0 (in the first portion of the formula) represents quarter 1. If you wanted the 4th quarter, then use =3. Brian wrote: On one tab I have the following data: Month Currency Amount 1 JPY 50.00 3 GBP 75.00 4 JPY 50.00 7 GBP 35.00 On another tab, I've setup the Sumproduct formula to give me the amounts that correspond with the correct currency, but how can I get a total by quarter? For example, on this other tab, I'd like to allow the user to select a month, then have the spreadsheet autopopulate the quarterly info to-date based on the month they selected... So if the user selects the month April and JPY...in the first column it will add together any JPY amount with a month less than or equal to March (3)...and it column two it will add together any amounts with a month of April (4)...and disregard anything beyond that. Any ideas how I can do this? I can get the first QTR column to work with the following formula... =(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000)),SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000))) But can't get this work for the periods 4, 5 & 6. Any ideas? -- Dave Peterson |
sumproduct by quarter
Maybe this:
E1 = user entered month = April F1 = JPY =SUMPRODUCT(--(CEILING(A2:A5/3,1)=CEILING(MONTH("1-"&E1)/3,1)),--(B2:B5="JPY"),C2:C5) -- Biff Microsoft Excel MVP "Brian" wrote in message ... Dave, Thanks...but this doesn't appear to do what I need it to... I want it to add the data in seperate columns depending on what quarter it occured in. If it's in the first qtr...I want it only to add month 1-3...but I want to give the person viewing the report the ability to choose the specific month. For example if the person only selects the month of Feb...I only want it to add together months 1&2...etc.... "Dave Peterson" wrote: =SUMPRODUCT(--(INT((A2:A99-1)/3)=0),--(B2:B99="jpy"),C2:C99) That 0 (in the first portion of the formula) represents quarter 1. If you wanted the 4th quarter, then use =3. Brian wrote: On one tab I have the following data: Month Currency Amount 1 JPY 50.00 3 GBP 75.00 4 JPY 50.00 7 GBP 35.00 On another tab, I've setup the Sumproduct formula to give me the amounts that correspond with the correct currency, but how can I get a total by quarter? For example, on this other tab, I'd like to allow the user to select a month, then have the spreadsheet autopopulate the quarterly info to-date based on the month they selected... So if the user selects the month April and JPY...in the first column it will add together any JPY amount with a month less than or equal to March (3)...and it column two it will add together any amounts with a month of April (4)...and disregard anything beyond that. Any ideas how I can do this? I can get the first QTR column to work with the following formula... =(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000)),SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000))) But can't get this work for the periods 4, 5 & 6. Any ideas? -- Dave Peterson |
sumproduct by quarter
Ooops! I forgot to reference the currency cell:
=SUMPRODUCT(--(CEILING(A2:A5/3,1)=CEILING(MONTH("1-"&E1)/3,1)),--(B2:B5=F1),C2:C5) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Maybe this: E1 = user entered month = April F1 = JPY =SUMPRODUCT(--(CEILING(A2:A5/3,1)=CEILING(MONTH("1-"&E1)/3,1)),--(B2:B5="JPY"),C2:C5) -- Biff Microsoft Excel MVP "Brian" wrote in message ... Dave, Thanks...but this doesn't appear to do what I need it to... I want it to add the data in seperate columns depending on what quarter it occured in. If it's in the first qtr...I want it only to add month 1-3...but I want to give the person viewing the report the ability to choose the specific month. For example if the person only selects the month of Feb...I only want it to add together months 1&2...etc.... "Dave Peterson" wrote: =SUMPRODUCT(--(INT((A2:A99-1)/3)=0),--(B2:B99="jpy"),C2:C99) That 0 (in the first portion of the formula) represents quarter 1. If you wanted the 4th quarter, then use =3. Brian wrote: On one tab I have the following data: Month Currency Amount 1 JPY 50.00 3 GBP 75.00 4 JPY 50.00 7 GBP 35.00 On another tab, I've setup the Sumproduct formula to give me the amounts that correspond with the correct currency, but how can I get a total by quarter? For example, on this other tab, I'd like to allow the user to select a month, then have the spreadsheet autopopulate the quarterly info to-date based on the month they selected... So if the user selects the month April and JPY...in the first column it will add together any JPY amount with a month less than or equal to March (3)...and it column two it will add together any amounts with a month of April (4)...and disregard anything beyond that. Any ideas how I can do this? I can get the first QTR column to work with the following formula... =(IF($A$23,SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=3)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000)),SUMPRODUCT(('Data 1st Half'!$B$4:$B$65000<=$A$2)*('Data 1st Half'!$E$4:$E$65000="JPY")*('Data 1st Half'!$G$4:$G$65000))) But can't get this work for the periods 4, 5 & 6. Any ideas? -- Dave Peterson |
| All times are GMT +1. The time now is 03:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com