| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
|
SUMIF function
=SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * M5:M31)
Look at this web site for more info... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Pieman" wrote: 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.programming
|
|||
|
|||
|
SUMIF function
Sorry should be =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * R5:R31)
-- HTH... Jim Thomlinson "Jim Thomlinson" wrote: =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * M5:M31) Look at this web site for more info... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Pieman" wrote: 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.programming
|
|||
|
|||
|
SUMIF function
You could use SUMIF like this =SUMIF(Websites!B5:B31,""&DATE(YEAR(NOW()),1,0),W ebsites!R5:R31)-SUMIF(Websites!B5:B31,""&DATE(YEAR(NOW())+1,1,0), Websites!R5:R31). -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516035 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIF function
Jim, thats great, thank you very much for your help. Do you know how I could
achieve the same result but looking for entries for the previous year instead of the current one? Thanks again "Jim Thomlinson" wrote: Sorry should be =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * R5:R31) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * M5:M31) Look at this web site for more info... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Pieman" wrote: 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.programming
|
|||
|
|||
|
SUMIF function
That would be =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())-1) * R5:R31) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=516035 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SUMIF function
You should consider using a pivot table. You can group on the date column by
year , month, quarter... Whatever floats your boat. If you have any questions about doing this post a new thread and we can work on it... -- HTH... Jim Thomlinson "Pieman" wrote: Jim, thats great, thank you very much for your help. Do you know how I could achieve the same result but looking for entries for the previous year instead of the current one? Thanks again "Jim Thomlinson" wrote: Sorry should be =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * R5:R31) -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: =SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())) * M5:M31) Look at this web site for more info... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Pieman" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
| How do I use the TODAY function with the SUMIF function? | Excel Worksheet Functions | |||
| SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
| Can SUMIF function include AND function | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Hybrid Mode
