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

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" <(E-Mail Removed)> wrote in message
news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >

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(TODAY()),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" <(E-Mail Removed)> wrote in message
> news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

Hi

=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())),Websites!R5:R31)


Arvi Laanemets



"Pieman" <(E-Mail Removed)> wrote in message
newsC9B3D44-B6D9-4F94-B8D8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > > 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
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Avri

I have entered this formula:
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5:R31).
But I still get a popup error message that says: 'The formula you typed
contains an error'.

Thanks
Simon

"Arvi Laanemets" wrote:

> Hi
>
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> ())),Websites!R5:R31)
>
>
> Arvi Laanemets
>
>
>
> "Pieman" <(E-Mail Removed)> wrote in message
> newsC9B3D44-B6D9-4F94-B8D8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > > > 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
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Hi

To locate the part of formula, causing the error, try to calculate different
parts of formula

=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1))
=SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())))
=SUMPRODUCT(Websites!R5:R31)
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())))
etc.

Arvi Laanemets


"Pieman" <(E-Mail Removed)> wrote in message
news:8BF771F1-1067-4FFA-BE55-(E-Mail Removed)...
> Avri
>
> I have entered this formula:
>
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())),Websites!R5:R31).
> But I still get a popup error message that says: 'The formula you typed
> contains an error'.
>
> Thanks
> Simon
>
> "Arvi Laanemets" wrote:
>
> > Hi
> >
> >
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> > ())),Websites!R5:R31)
> >
> >
> > Arvi Laanemets
> >
> >
> >
> > "Pieman" <(E-Mail Removed)> wrote in message
> > newsC9B3D44-B6D9-4F94-B8D8-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > > > > 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
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Avri

I have tried all the parts of the formula and the only one that causes the
error is:

=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY())))

All these worked fine:
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1))
=SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())))
=SUMPRODUCT(Websites!R5:R31)

Any idea where the error is located?

Many thanks
Simon

"Arvi Laanemets" wrote:

> Hi
>
> To locate the part of formula, causing the error, try to calculate different
> parts of formula
>
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1))
> =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())))
> =SUMPRODUCT(Websites!R5:R31)
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> ())))
> etc.
>
> Arvi Laanemets
>
>
> "Pieman" <(E-Mail Removed)> wrote in message
> news:8BF771F1-1067-4FFA-BE55-(E-Mail Removed)...
> > Avri
> >
> > I have entered this formula:
> >
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> ())),Websites!R5:R31).
> > But I still get a popup error message that says: 'The formula you typed
> > contains an error'.
> >
> > Thanks
> > Simon
> >
> > "Arvi Laanemets" wrote:
> >
> > > Hi
> > >
> > >
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> > > ())),Websites!R5:R31)
> > >
> > >
> > > Arvi Laanemets
> > >
> > >
> > >
> > > "Pieman" <(E-Mail Removed)> wrote in message
> > > newsC9B3D44-B6D9-4F94-B8D8-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > > > > > 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
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Hi

I tried to det the same error with same formula, and all partial formulas
working properly - I get it only, when referred ranges were different
dimensions. Something like
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B131)=YEAR(TODA
Y())))

So check, that all referred ranges contain cells from 22 rows, and all of
them are single-column ranges.


Arvi Laanemets


"Pieman" <(E-Mail Removed)> wrote in message
news:B3E262F2-0CE5-4C8D-AEB0-(E-Mail Removed)...
> Avri
>
> I have tried all the parts of the formula and the only one that causes the
> error is:
>
>
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
())))
>
> All these worked fine:
> =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1))
> =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())))
> =SUMPRODUCT(Websites!R5:R31)
>
> Any idea where the error is located?
>
> Many thanks
> Simon
>
> "Arvi Laanemets" wrote:
>
> > Hi
> >
> > To locate the part of formula, causing the error, try to calculate
different
> > parts of formula
> >
> > =SUMPRODUCT(--(MONTH(Websites!B5:B31)=1))
> > =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())))
> > =SUMPRODUCT(Websites!R5:R31)
> >
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> > ())))
> > etc.
> >
> > Arvi Laanemets
> >
> >
> > "Pieman" <(E-Mail Removed)> wrote in message
> > news:8BF771F1-1067-4FFA-BE55-(E-Mail Removed)...
> > > Avri
> > >
> > > I have entered this formula:
> > >
> >
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> > ())),Websites!R5:R31).
> > > But I still get a popup error message that says: 'The formula you
typed
> > > contains an error'.
> > >
> > > Thanks
> > > Simon
> > >
> > > "Arvi Laanemets" wrote:
> > >
> > > > Hi
> > > >
> > > >
> >
=SUMPRODUCT(--(MONTH(Websites!B5:B31)=1),--(YEAR(Websites!B5:B31)=YEAR(TODAY
> > > > ())),Websites!R5:R31)
> > > >
> > > >
> > > > Arvi Laanemets
> > > >
> > > >
> > > >
> > > > "Pieman" <(E-Mail Removed)> wrote in message
> > > > newsC9B3D44-B6D9-4F94-B8D8-(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:0555337A-34D1-4BB3-96B1-(E-Mail Removed)...
> > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > > news:49F46F89-167D-4F49-BD4B-(E-Mail Removed)...
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >