Calculating monthly totals for current and previous year
Does anyone know the best formula for totalling values in a column that
correspond to each month of the current and previous year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. I have tried the following formulas but keep getting a popup box saying that the formula contains an error: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())-1),Websites!R5:R31) Any ideas would be greatly appreciated. Thanks Simon |
Calculating monthly totals for current and previous year
One way ..
In A2: 01-Dec-2005 In B2: 31-Dec-2005 Then in say, C2: =SUMPRODUCT((Websites!$B$5:$B$31=A2)*(Websites!$B $5:$B$31<=B2),Websites!$R$ 5:$R$31) will return the total commission for Dec 2005 C2 can be copied down to calc accordingly for other month / year stipulated in cols A and B (in A3:B3, A4:B4, etc) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Pieman" wrote in message ... Does anyone know the best formula for totalling values in a column that correspond to each month of the current and previous year? I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet. I have tried the following formulas but keep getting a popup box saying that the formula contains an error: =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())),Websites!R5:R31) =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY ())-1),Websites!R5:R31) Any ideas would be greatly appreciated. Thanks Simon |
All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com