Change it to

=SUMPRODUCT((YEAR(A2:A86)=1996)*(AE2:AO86))



--
Regards,

Peo Sjoblom

(No private emails please)


"heater" wrote in message
...
I need a similar formula as =SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100),
but
If A2:A100 = 1996, then it will count the total in cells AE2:AO86. I
substituted AE2:AO86 where B2:B100 is, but I get #VALUE!.

"Peo Sjoblom" wrote:

One way, assume dates in A2:A100 and the values you want to total in
B2:B100, adapt to fit your own ranges, if you meant you ant the total
first
for 1996, then a total for 1997 and so on

=SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100)

if you want all years from 1996 to 2005

=SUMPRODUCT(--(YEAR(A2:A100)=1996),B2:B100)

it will only work if the dates are real dates and thus numerical

--
Regards,

Peo Sjoblom

(No private emails please)


"heater" wrote in message
...
I need to count a cell if another cell is in a particular year. For
example:
Column S has dates in this format (May-05). The dates range from
Jan-96
to
Aug-05. The numbers to be added are in column W. So, I need a formula
that
will calcuate a total for 1996, 1997, 1998, through 2005.