Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add and IF function to a SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Sumif function with remote cell references | Excel Worksheet Functions | |||
SUMIF function | Excel Worksheet Functions |