sumproduct in a given year

B

Bumblebee

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
 
D

Dave F

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
 
B

Bumblebee

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

Bumblebee

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
 
B

Biff

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
 
B

Bumblebee

Thank you again

Biff said:
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
Click to expand...
 
Top