sumproduct in a given year

Hi, can someone help me with this:

the following formula works:

SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))

if I stick in the year in the formula, e.g. 2005 as in above. But when I
reference it to another cell that puts 31/12/2005 I get 0 as an answer. How
do I get it to understand that I am interested in the 2005 bit. I don't know
if I am making myself understood. I want to sume a list of things in a given
year, 2004, 2005, but the year part referenced to another cell that has the
date looking like this 31/12/aaaa

Thanks

You can create a helper column which holds the year and change the formula to
reference the helper column.

Assume the helper column is column D:

=SUMPRODUCT((YEAR(B17:B53)=H$1)*C17:C53))

Dave
--
Brevity is the soul of wit.


"Bumblebee" wrote:

> Hi, can someone help me with this:
>
> the following formula works:
>
> SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
>
> if I stick in the year in the formula, e.g. 2005 as in above. But when I
> reference it to another cell that puts 31/12/2005 I get 0 as an answer. How
> do I get it to understand that I am interested in the 2005 bit. I don't know
> if I am making myself understood. I want to sume a list of things in a given
> year, 2004, 2005, but the year part referenced to another cell that has the
> date looking like this 31/12/aaaa
>
> Thanks

Hi!

Try this:

A1 = 31/12/2005

=SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)

Biff

"Bumblebee" <[email protected]> wrote in message
news:[email protected]...
> Hi, can someone help me with this:
>
> the following formula works:
>
> SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
>
> if I stick in the year in the formula, e.g. 2005 as in above. But when I
> reference it to another cell that puts 31/12/2005 I get 0 as an answer.
> How
> do I get it to understand that I am interested in the 2005 bit. I don't
> know
> if I am making myself understood. I want to sume a list of things in a
> given
> year, 2004, 2005, but the year part referenced to another cell that has
> the
> date looking like this 31/12/aaaa
>
> Thanks

Thanks but that is exactly what I was trying to do but didn't work. I guess I
really didn't make myself understood. What works is what Biff suggested.

"Dave F" wrote:

> You can create a helper column which holds the year and change the formula to
> reference the helper column.
>
> Assume the helper column is column D:
>
> =SUMPRODUCT((YEAR(B17:B53)=H$1)*C17:C53))
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "Bumblebee" wrote:
>
> > Hi, can someone help me with this:
> >
> > the following formula works:
> >
> > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
> >
> > if I stick in the year in the formula, e.g. 2005 as in above. But when I
> > reference it to another cell that puts 31/12/2005 I get 0 as an answer. How
> > do I get it to understand that I am interested in the 2005 bit. I don't know
> > if I am making myself understood. I want to sume a list of things in a given
> > year, 2004, 2005, but the year part referenced to another cell that has the
> > date looking like this 31/12/aaaa
> >
> > Thanks

Thanks it worked

"Biff" wrote:

> Hi!
>
> Try this:
>
> A1 = 31/12/2005
>
> =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)
>
> Biff
>
> "Bumblebee" <[email protected]> wrote in message
> news:[email protected]...
> > Hi, can someone help me with this:
> >
> > the following formula works:
> >
> > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
> >
> > if I stick in the year in the formula, e.g. 2005 as in above. But when I
> > reference it to another cell that puts 31/12/2005 I get 0 as an answer.
> > How
> > do I get it to understand that I am interested in the 2005 bit. I don't
> > know
> > if I am making myself understood. I want to sume a list of things in a
> > given
> > year, 2004, 2005, but the year part referenced to another cell that has
> > the
> > date looking like this 31/12/aaaa
> >
> > Thanks
>
>
>

One more question if you don't mind, if instead of the sum I want the average
of the twelve entries (you see there is one for each month of the year) would
I have to stick AVERAGE somewhere in the formula or would it be a different
function altogether

"Bumblebee" wrote:

> Thanks it worked
>
> "Biff" wrote:
>
> > Hi!
> >
> > Try this:
> >
> > A1 = 31/12/2005
> >
> > =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)
> >
> > Biff
> >
> > "Bumblebee" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi, can someone help me with this:
> > >
> > > the following formula works:
> > >
> > > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
> > >
> > > if I stick in the year in the formula, e.g. 2005 as in above. But when I
> > > reference it to another cell that puts 31/12/2005 I get 0 as an answer.
> > > How
> > > do I get it to understand that I am interested in the 2005 bit. I don't
> > > know
> > > if I am making myself understood. I want to sume a list of things in a
> > > given
> > > year, 2004, 2005, but the year part referenced to another cell that has
> > > the
> > > date looking like this 31/12/aaaa
> > >
> > > Thanks
> >
> >
> >

If you're absolutely sure there are only 12 entries (and will always be only
12 entries) that meet the YEAR criteria then just add this to the end of the
formula: /12

Just to be on the safe side I would use this array formula. Entered using
the key combination of CTRL,SHIFT,ENTER (not just ENTER):

=AVERAGE(IF(YEAR(B17:B53)=YEAR(A1),C17:C53))

Biff

"Bumblebee" <[email protected]> wrote in message
news:[email protected]...
> One more question if you don't mind, if instead of the sum I want the
> average
> of the twelve entries (you see there is one for each month of the year)
> would
> I have to stick AVERAGE somewhere in the formula or would it be a
> different
> function altogether
>
> "Bumblebee" wrote:
>
>> Thanks it worked
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > Try this:
>> >
>> > A1 = 31/12/2005
>> >
>> > =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)
>> >
>> > Biff
>> >
>> > "Bumblebee" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Hi, can someone help me with this:
>> > >
>> > > the following formula works:
>> > >
>> > > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
>> > >
>> > > if I stick in the year in the formula, e.g. 2005 as in above. But
>> > > when I
>> > > reference it to another cell that puts 31/12/2005 I get 0 as an
>> > > answer.
>> > > How
>> > > do I get it to understand that I am interested in the 2005 bit. I
>> > > don't
>> > > know
>> > > if I am making myself understood. I want to sume a list of things in
>> > > a
>> > > given
>> > > year, 2004, 2005, but the year part referenced to another cell that
>> > > has
>> > > the
>> > > date looking like this 31/12/aaaa
>> > >
>> > > Thanks
>> >
>> >
>> >

Thank you again

"Biff" wrote:

> If you're absolutely sure there are only 12 entries (and will always be only
> 12 entries) that meet the YEAR criteria then just add this to the end of the
> formula: /12
>
> Just to be on the safe side I would use this array formula. Entered using
> the key combination of CTRL,SHIFT,ENTER (not just ENTER):
>
> =AVERAGE(IF(YEAR(B17:B53)=YEAR(A1),C17:C53))
>
> Biff
>
> "Bumblebee" <[email protected]> wrote in message
> news:[email protected]...
> > One more question if you don't mind, if instead of the sum I want the
> > average
> > of the twelve entries (you see there is one for each month of the year)
> > would
> > I have to stick AVERAGE somewhere in the formula or would it be a
> > different
> > function altogether
> >
> > "Bumblebee" wrote:
> >
> >> Thanks it worked
> >>
> >> "Biff" wrote:
> >>
> >> > Hi!
> >> >
> >> > Try this:
> >> >
> >> > A1 = 31/12/2005
> >> >
> >> > =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)
> >> >
> >> > Biff
> >> >
> >> > "Bumblebee" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > Hi, can someone help me with this:
> >> > >
> >> > > the following formula works:
> >> > >
> >> > > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
> >> > >
> >> > > if I stick in the year in the formula, e.g. 2005 as in above. But
> >> > > when I
> >> > > reference it to another cell that puts 31/12/2005 I get 0 as an
> >> > > answer.
> >> > > How
> >> > > do I get it to understand that I am interested in the 2005 bit. I
> >> > > don't
> >> > > know
> >> > > if I am making myself understood. I want to sume a list of things in
> >> > > a
> >> > > given
> >> > > year, 2004, 2005, but the year part referenced to another cell that
> >> > > has
> >> > > the
> >> > > date looking like this 31/12/aaaa
> >> > >
> >> > > Thanks
> >> >
> >> >
> >> >
>
>
>

You're welcome!

Biff

"Bumblebee" <[email protected]> wrote in message
news:[email protected]...
> Thank you again
>
> "Biff" wrote:
>
>> If you're absolutely sure there are only 12 entries (and will always be
>> only
>> 12 entries) that meet the YEAR criteria then just add this to the end of
>> the
>> formula: /12
>>
>> Just to be on the safe side I would use this array formula. Entered using
>> the key combination of CTRL,SHIFT,ENTER (not just ENTER):
>>
>> =AVERAGE(IF(YEAR(B17:B53)=YEAR(A1),C17:C53))
>>
>> Biff
>>
>> "Bumblebee" <[email protected]> wrote in message
>> news:[email protected]...
>> > One more question if you don't mind, if instead of the sum I want the
>> > average
>> > of the twelve entries (you see there is one for each month of the year)
>> > would
>> > I have to stick AVERAGE somewhere in the formula or would it be a
>> > different
>> > function altogether
>> >
>> > "Bumblebee" wrote:
>> >
>> >> Thanks it worked
>> >>
>> >> "Biff" wrote:
>> >>
>> >> > Hi!
>> >> >
>> >> > Try this:
>> >> >
>> >> > A1 = 31/12/2005
>> >> >
>> >> > =SUMPRODUCT(--(YEAR(B17:B53)=YEAR(A1)),C17:C53)
>> >> >
>> >> > Biff
>> >> >
>> >> > "Bumblebee" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > Hi, can someone help me with this:
>> >> > >
>> >> > > the following formula works:
>> >> > >
>> >> > > SUMPRODUCT((YEAR(B17:B53)=2005)*(C17:C53))
>> >> > >
>> >> > > if I stick in the year in the formula, e.g. 2005 as in above. But
>> >> > > when I
>> >> > > reference it to another cell that puts 31/12/2005 I get 0 as an
>> >> > > answer.
>> >> > > How
>> >> > > do I get it to understand that I am interested in the 2005 bit. I
>> >> > > don't
>> >> > > know
>> >> > > if I am making myself understood. I want to sume a list of things
>> >> > > in
>> >> > > a
>> >> > > given
>> >> > > year, 2004, 2005, but the year part referenced to another cell
>> >> > > that
>> >> > > has
>> >> > > the
>> >> > > date looking like this 31/12/aaaa
>> >> > >
>> >> > > Thanks
>> >> >
>> >> >
>> >> >
>>
>>
>>