sumproduct in a given year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bumblebee - 18 Aug 2006 22:21 GMT

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

Reply to this Message

Dave F - 18 Aug 2006 22:46 GMT

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

Signature

Brevity is the soul of wit.

> Hi, can someone help me with this:
>
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Bumblebee - 18 Aug 2006 23:03 GMT

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.

> You can create a helper column which holds the year and change the formula to
> reference the helper column.
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message

Biff - 18 Aug 2006 22:51 GMT

Hi!

Try this:

A1 = 31/12/2005

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

Biff

> Hi, can someone help me with this:
>
[quoted text clipped - 14 lines]
>
> Thanks

Reply to this Message

Bumblebee - 18 Aug 2006 23:03 GMT

Thanks it worked

> Hi!
>
[quoted text clipped - 24 lines]
> >
> > Thanks

Reply to this Message

Bumblebee - 18 Aug 2006 23:10 GMT

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

> Thanks it worked
>
[quoted text clipped - 26 lines]
> > >
> > > Thanks

Reply to this Message

Biff - 18 Aug 2006 23:27 GMT

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

> One more question if you don't mind, if instead of the sum I want the
> average
[quoted text clipped - 39 lines]
>> > >
>> > > Thanks

Reply to this Message

Bumblebee - 19 Aug 2006 07:20 GMT

Thank you again

> 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
[quoted text clipped - 50 lines]
> >> > >
> >> > > Thanks

Reply to this Message

Biff - 19 Aug 2006 18:59 GMT

You're welcome!

Biff

> Thank you again
>
[quoted text clipped - 56 lines]
>> >> > >
>> >> > > Thanks

Reply to this Message