What am I doing wrong with SUMPRODUCT

[jim101]

I have dates in Sheet1 column B and amounts in Column F, on another sheet I have 2021 in cell A133 and this formula in a cell but it does not give me the right answer?


=SUMPRODUCT((YEAR(Sheet1!$B$3:$B$2000)=$A133),Sheet1!F$3:F$2000)


How do I look at sheet1 B3:B200 for the year that is in A133 on another sheet and sum what is in Sheet1 F3:F2000 that is for that year?

I am using Excel 2003

Thanks

 

[Joe4]

Try this:

=SUMPRODUCT(--(YEAR(Sheet1!$B$3:$B$2000)=$A$133),Sheet1!F$3:F$2000)

(I believe you need to coerce the boolean result of your formula to a value).

 

[jim101]

Thanks that did it!

 

[Joe4]

You are welcome.