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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>