Calculating monthly totals
Hi
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())),Websites!R5:R31)
Arvi Laanemets
"Pieman" wrote in message
...
Avri, thanks for explaining that.
I have entered the following formula for the current year:
=SUMPRODUCT("--(MONTH(Websites!B5:B31)=01,--(YEAR(Websites!B5:B31)=YEAR(TODA
Y()),Websites!R5:R31)")
but I just get #VALUE! in the cell where the monthly commission total
should
be displayed.
Have you any ideas why this is?
Thanks
Simon
"Arvi Laanemets" wrote:
Hi
SaleDate and Commission are ranges, where sale dates and commission
figures
are stored. You can use there range references or named ranges. NB. Both
ranges SaleDates and Commission MUST be of same dimension.
When SalaDate doesn't contain valid format dates, then you have to
modify
the formula - how, depends on your data then.
Arvi Laanemets
"Pieman" wrote in message
...
Thanks Arvi, does SaleDate have to be replaced by the month required
or is
that the column title?
Many thanks
Simon
"Arvi Laanemets" wrote:
Hi
To return summary commissions for January of current year
=SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()),Commission)
The same for last year
=SUMPRODUCT(--(MONTH(SaleDate)=1,--(YEAR(SaleDate)=YEAR(TODAY()-1),Commissio
n)
Arvi Laanemets
"Pieman" wrote in message
...
Does anyone know the best formula for adding figures in a column
that
correspond to each month of the 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.
Any ideas would be greatly appreciated.
Thanks
Simon
|