SUMPRODUCT question

[jzingman]

I have in my running log:

=SUMPRODUCT((YEAR(Run!A$3:A$1008)=(ROW()+2001))*Run!C$3:C$1008)

which works just fine.
This seems to be a waste of sumproduct, so I tried replacing the * with a ,

=SUMPRODUCT((YEAR(Run!A$3:A$1008)=(ROW()+2001)),Run!C$3:C$1008)

This yields 0. When I debug, I see that there are a series of nonzero
values for each entry, but the sumproduct is still 0.


Using CTRL+SHIFT+ENTER doesn't help.

 

[Dave Peterson]

Try:
=SUMPRODUCT(--(YEAR(Run!A$3:A$1008)=(ROW()+2001)),Run!C$3:C$1008)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html