SUMIF function

[Pieman]

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

[Jim Thomlinson]

=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
>

[Jim Thomlinson]

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
> >

[daddylonglegs]

You could use SUMIF like this

=SUMIF(Websites!B5:B31,">"&DATE(YEAR(NOW()),1,0),Websites!R5:R31)-SUMIF(Websites!B5:B31,">"&DATE(YEAR(NOW())+1,1,0),Websites!R5:R31).

[Pieman]

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
> > >

[daddylonglegs]

That would be

=SUMPRODUCT((YEAR(B5:B31) =YEAR(TODAY())-1) * R5:R31)

[Jim Thomlinson]

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
> > > >