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

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

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

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

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

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

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