software

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(),quot;yyyyquot;).

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

Thanks
Simon

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Piemanquot; gt; wrote in message
...
gt; Hi, please help...
gt;
gt; I am trying to total up commission figures in a column that match a
specific
gt; year. Each row contains a cell for the date it was entered, the customer
gt; details and commission earnt.
gt;
gt; I have used the SUMIF funtion so far but cannot find the correct criteria
gt; for it to identify the year in the date cell and include the commission in
gt; the SUM if the year matches the criteria.
gt;
gt; The formula I have used so far is:
=SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt;
gt; F2 refers to a cell with the current year automatically entered by using
the
gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt;
gt; If anyone knows how to achieve this I would be eternally grateful.
gt;
gt; Thanks
gt; Simon
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

quot;Bob Phillipsquot; wrote:

gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Piemanquot; gt; wrote in message
gt; ...
gt; gt; Hi, please help...
gt; gt;
gt; gt; I am trying to total up commission figures in a column that match a
gt; specific
gt; gt; year. Each row contains a cell for the date it was entered, the customer
gt; gt; details and commission earnt.
gt; gt;
gt; gt; I have used the SUMIF funtion so far but cannot find the correct criteria
gt; gt; for it to identify the year in the date cell and include the commission in
gt; gt; the SUM if the year matches the criteria.
gt; gt;
gt; gt; The formula I have used so far is:
gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt;
gt; gt; F2 refers to a cell with the current year automatically entered by using
gt; the
gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt;
gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt;
gt; gt; Thanks
gt; gt; Simon
gt;
gt;
gt;

Just

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

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Piemanquot; gt; wrote in message
...
gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt;
gt; Could you tell me how to do the same but couting the number of cells in
the
gt; 'Websites!R5:R31' range that contain the current year?
gt;
gt; Thanks again
gt; Simon
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi, please help...
gt; gt; gt;
gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; specific
gt; gt; gt; year. Each row contains a cell for the date it was entered, the
customer
gt; gt; gt; details and commission earnt.
gt; gt; gt;
gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
criteria
gt; gt; gt; for it to identify the year in the date cell and include the
commission in
gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt;
gt; gt; gt; The formula I have used so far is:
gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt;
gt; gt; gt; F2 refers to a cell with the current year automatically entered by
using
gt; gt; the
gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt;
gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt; Simon
gt; gt;
gt; gt;
gt; gt;
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

quot;Bob Phillipsquot; wrote:

gt; Just
gt;
gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Piemanquot; gt; wrote in message
gt; ...
gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt;
gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; the
gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt;
gt; gt; Thanks again
gt; gt; Simon
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt;
gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; specific
gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; customer
gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt;
gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; criteria
gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; commission in
gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt;
gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt;
gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; using
gt; gt; gt; the
gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt;
gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks
gt; gt; gt; gt; Simon
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

Subtract from today's year?

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

Pieman wrote:
gt;
gt; Brilliant, works great thank you again. How would I achieve the same function
gt; for entries under the previous year instead of the current one?
gt;
gt; Thank you
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Just
gt; gt;
gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt; gt;
gt; gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; gt; the
gt; gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt; gt;
gt; gt; gt; Thanks again
gt; gt; gt; Simon
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; gt; specific
gt; gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; gt; customer
gt; gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; gt; criteria
gt; gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; gt; commission in
gt; gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; gt; using
gt; gt; gt; gt; the
gt; gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

--

Dave Peterson

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

quot;Dave Petersonquot; wrote:

gt; Subtract from today's year?
gt;
gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY()-1)))
gt;
gt; Pieman wrote:
gt; gt;
gt; gt; Brilliant, works great thank you again. How would I achieve the same function
gt; gt; for entries under the previous year instead of the current one?
gt; gt;
gt; gt; Thank you
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Just
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt; gt; gt;
gt; gt; gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; gt; gt; the
gt; gt; gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks again
gt; gt; gt; gt; Simon
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; gt; gt; specific
gt; gt; gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; gt; gt; customer
gt; gt; gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; gt; gt; criteria
gt; gt; gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; gt; gt; commission in
gt; gt; gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; gt; gt; using
gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

quot;Piemanquot; wrote:

gt; Thanks Dave, I've tried the formula you suggested but the result shows just
gt; one record for 2005 when in fact there are 26 for 2005 and just one for 2006.
gt;
gt; Thanks
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Subtract from today's year?
gt; gt;
gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY()-1)))
gt; gt;
gt; gt; Pieman wrote:
gt; gt; gt;
gt; gt; gt; Brilliant, works great thank you again. How would I achieve the same function
gt; gt; gt; for entries under the previous year instead of the current one?
gt; gt; gt;
gt; gt; gt; Thank you
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Just
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; gt; gt; gt; the
gt; gt; gt; gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks again
gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; gt; gt; gt; specific
gt; gt; gt; gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; gt; gt; gt; customer
gt; gt; gt; gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; gt; gt; gt; criteria
gt; gt; gt; gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; gt; gt; gt; commission in
gt; gt; gt; gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; gt; gt; gt; using
gt; gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

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

quot;bpeltzerquot; wrote:

gt; I think the parens were wrong in the prior formula, so that it was comparing
gt; to the year of yesterday's date, rather than last year. Try:
gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())-1))
gt; --Bruce
gt;
gt; quot;Piemanquot; wrote:
gt;
gt; gt; Thanks Dave, I've tried the formula you suggested but the result shows just
gt; gt; one record for 2005 when in fact there are 26 for 2005 and just one for 2006.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Subtract from today's year?
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY()-1)))
gt; gt; gt;
gt; gt; gt; Pieman wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; Brilliant, works great thank you again. How would I achieve the same function
gt; gt; gt; gt; for entries under the previous year instead of the current one?
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Just
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks again
gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; gt; gt; gt; gt; specific
gt; gt; gt; gt; gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; gt; gt; gt; gt; customer
gt; gt; gt; gt; gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; gt; gt; gt; gt; criteria
gt; gt; gt; gt; gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; gt; gt; gt; gt; commission in
gt; gt; gt; gt; gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; gt; gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; gt; gt; gt; gt; using
gt; gt; gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;

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.quot;Piemanquot; wrote:

gt; Thanks Bruce that works great. How do i do the same for commissions in a
gt; seperate column on the same worksheet. The current formula I have for summing
gt; the total commissions for the current year is:
gt; =SUMPRODUCT(--(YEAR(Websites!B5:B500)=YEAR(TODAY())),Websites!R5 :R500). I
gt; have tried inputting a -1 after the TODAY function but this gives am
gt; incorrect figure. The B column contains the date of the entry and the R
gt; column contains the commissions.
gt;
gt; I would really appreciate your advice.
gt;
gt; Thanks
gt; Simon
gt;
gt;
gt;
gt;
gt; quot;bpeltzerquot; wrote:
gt;
gt; gt; I think the parens were wrong in the prior formula, so that it was comparing
gt; gt; to the year of yesterday's date, rather than last year. Try:
gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())-1))
gt; gt; --Bruce
gt; gt;
gt; gt; quot;Piemanquot; wrote:
gt; gt;
gt; gt; gt; Thanks Dave, I've tried the formula you suggested but the result shows just
gt; gt; gt; one record for 2005 when in fact there are 26 for 2005 and just one for 2006.
gt; gt; gt;
gt; gt; gt; Thanks
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Subtract from today's year?
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY()-1)))
gt; gt; gt; gt;
gt; gt; gt; gt; Pieman wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Brilliant, works great thank you again. How would I achieve the same function
gt; gt; gt; gt; gt; for entries under the previous year instead of the current one?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thank you
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Just
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!R5:R31)=YEAR(TODAY())))
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; gt; Hi Bob, thats great, it works perfect. Thank you very much.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Could you tell me how to do the same but couting the number of cells in
gt; gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; gt; 'Websites!R5:R31' range that contain the current year?
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks again
gt; gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; =SUMPRODUCT(--(YEAR(Websites!B5:B31)=YEAR(TODAY())),Websites!R5: R31)
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; gt; gt; gt; HTH
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; quot;Piemanquot; gt; wrote in message
gt; gt; gt; gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; gt; gt; gt; Hi, please help...
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; I am trying to total up commission figures in a column that match a
gt; gt; gt; gt; gt; gt; gt; gt; specific
gt; gt; gt; gt; gt; gt; gt; gt; gt; year. Each row contains a cell for the date it was entered, the
gt; gt; gt; gt; gt; gt; customer
gt; gt; gt; gt; gt; gt; gt; gt; gt; details and commission earnt.
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; I have used the SUMIF funtion so far but cannot find the correct
gt; gt; gt; gt; gt; gt; criteria
gt; gt; gt; gt; gt; gt; gt; gt; gt; for it to identify the year in the date cell and include the
gt; gt; gt; gt; gt; gt; commission in
gt; gt; gt; gt; gt; gt; gt; gt; gt; the SUM if the year matches the criteria.
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; The formula I have used so far is:
gt; gt; gt; gt; gt; gt; gt; gt; =SUMIF(Websites!B5:B31,F2,Websites!R5:R31).
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; F2 refers to a cell with the current year automatically entered by
gt; gt; gt; gt; gt; gt; using
gt; gt; gt; gt; gt; gt; gt; gt; the
gt; gt; gt; gt; gt; gt; gt; gt; gt; TEXT function: =TEXT(TODAY(),quot;yyyyquot;).
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; If anyone knows how to achieve this I would be eternally grateful.
gt; gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt; gt; Thanks
gt; gt; gt; gt; gt; gt; gt; gt; gt; Simon
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;