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

sumproduct in a given year
 

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

sumproduct in a given year
 

Hi!

Try this:

A1 = 31/12/2005

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

Biff

"Bumblebee" wrote in message
...
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

sumproduct in a given year
 

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

sumproduct in a given year
 

Thanks it worked

"Biff" wrote:

Hi!

Try this:

A1 = 31/12/2005

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

Biff

"Bumblebee" wrote in message
...
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

sumproduct in a given year
 

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" wrote in message
...
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

sumproduct in a given year
 

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" wrote in message
...
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" wrote in message
...
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

sumproduct in a given year
 

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" wrote in message
...
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" wrote in message
...
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

sumproduct in a given year
 

You're welcome!

Biff

"Bumblebee" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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