We're always told to test at the limits of the data, but that's taking it a
bit far Dave <bg

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Dave Peterson" wrote in message
...
My formula would work perfectly on January 1st <vbg.

bpeltzer wrote:

I'd expect
=SUMPRODUCT(--(YEAR(Websites!B5:B500)=YEAR(TODAY())-1),Websites!R5:R500)
to
work. Same idea as the other, the -1 doesn't come after TODAY
(today()-1 is
yesterday), but the closing paren of the YEAR function year(today())-1.

"Pieman" wrote:

Thanks Bruce that works great. How do i do the same for commissions in
a
seperate column on the same worksheet. The current formula I have for
summing
the total commissions for the current year is:

=SUMPRODUCT(--(YEAR(Websites!B5:B500)=YEAR(TODAY())),Websites!R5 :R500). I
have tried inputting a -1 after the TODAY function but this gives am
incorrect figure. The B column contains the date of the entry and the
R
column contains the commissions.

I would really appreciate your advice.

Thanks
Simon




"bpeltzer" wrote:

I think the parens were wrong in the prior formula, so that it was
comparing
to the year of yesterday's date, rather than last year. Try:
=SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())-1))
--Bruce

"Pieman" wrote:

Thanks Dave, I've tried the formula you suggested but the result
shows just
one record for 2005 when in fact there are 26 for 2005 and just
one for 2006.

Thanks

"Dave Peterson" wrote:

Subtract from today's year?

=SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY()-1)))

Pieman wrote:

Brilliant, works great thank you again. How would I achieve
the same function
for entries under the previous year instead of the current
one?

Thank you

"Bob Phillips" wrote:

Just

=SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pieman" wrote in message
...
Hi Bob, thats great, it works perfect. Thank you very
much.

Could you tell me how to do the same but couting the
number of cells in
the
'Websites!R5:R31' range that contain the current year?

Thanks again
Simon

"Bob Phillips" wrote:


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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pieman" wrote in
message

...
Hi, please help...

I am trying to total up commission figures in a column
that match a
specific
year. Each row contains a cell for the date it was
entered, the
customer
details and commission earnt.

I have used the SUMIF funtion so far but cannot find
the correct
criteria
for it to identify the year in the date cell and
include the
commission in
the SUM if the year matches the criteria.

The formula I have used so far is:
=SUMIF(Websites!B5:B31,F2,Websites!R5:R31).

F2 refers to a cell with the current year
automatically entered by
using
the
TEXT function: =TEXT(TODAY(),"yyyy").

If anyone knows how to achieve this I would be
eternally grateful.

Thanks
Simon







--

Dave Peterson


--

Dave Peterson