Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

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
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default SUMIF function

=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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

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




  #4   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default SUMIF function

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






  #5   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

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








  #6   Report Post  
Posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default SUMIF function

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
  #7   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

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

  #8   Report Post  
Posted to microsoft.public.excel.newusers
bpeltzer
 
Posts: n/a
Default SUMIF function

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

  #9   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

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

  #10   Report Post  
Posted to microsoft.public.excel.newusers
bpeltzer
 
Posts: n/a
Default SUMIF function

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



  #11   Report Post  
Posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default SUMIF function

Thanks for the correction.

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
  #12   Report Post  
Posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default SUMIF function

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
  #13   Report Post  
Posted to microsoft.public.excel.newusers
Bob Phillips
 
Posts: n/a
Default SUMIF function

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



  #14   Report Post  
Posted to microsoft.public.excel.newusers
Dave Peterson
 
Posts: n/a
Default SUMIF function

It was only a bug 365/366 or 364/365 of the time.

It was a feature the rest.

Bob Phillips wrote:

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


--

Dave Peterson
  #15   Report Post  
Posted to microsoft.public.excel.newusers
Pieman
 
Posts: n/a
Default SUMIF function

Bruce, yep you were correct. The formula works perfect, I was using an
incorrect figure as the comparison.

Do you know how to compare the % difference of two figures + or - etc. I
need to quantify how commission figures compare to targets.

Many thanks
Simon

"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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I add and IF function to a SUMIF function? adscrim Excel Worksheet Functions 4 January 21st 06 01:32 PM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 04:24 PM
SumIF function ACDenver Excel Discussion (Misc queries) 2 August 17th 05 09:47 PM
Sumif function with remote cell references hennis Excel Worksheet Functions 1 August 12th 05 01:54 AM
SUMIF function yak10 Excel Worksheet Functions 0 February 12th 05 06:12 PM


All times are GMT +1. The time now is 01:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"