sumif where criteria range is an array of dates and condition is a year

[barrfly]

=SUMIF(--(year($C$1:$C$500)),$F1,D$1:D$500) - does not work

I have figured out how to make a sumproduct work where the conditional data is an array of dates and I want to isolate the adjacent data to the dates that are in a certain year. like below.

=SUMPRODUCT(--(YEAR($B$30:$B$113)=$B51),$C$30:$C$113) - works

can someone please let me know how to make the above sumif work?

[DonkeyOte]

You can't amend the source values in the SUMIF I'm afraid - as you can with SUMPRODUCT.

For a SUMIF approach you would need to combine two:

=SUMIF($C$1:$C$500,">="&DATE($F1,1,1),D$1:D$500)-SUMIF($C$1:$C$500,">="&DATE($F1+1,1,1),D$1:D$500)
(assumes F1 is an integer)

If you're using XL2007 you could use SUMIFS of course

=SUMIFS(D$1:D$500,$C$1:$C$500,">="&DATE($F1,1,1),$C$1:$C$500,"<"&DATE($F1+1,1,1))

[NBVC]

The first range cannot be a calculated array in the SUMIF function.

You can add a helper column =YEAR(A1) copied down and SUMIF on that...

[barrfly]

thanks for the responses. I have been trying to avoid adding the superfluous helper columns, but ended up doing it to save some time. I will inject Donkey's solution shortly. Thanks again.