Reading between the lines, I'm guessing that the part about (year=b1)
is comparing a number with text string, something to be avoided.

Maybe it'd help to use (year=VALUE(b1)) instead.

Personally, my preference would be to use (year=YEAR(A1)) and dispense
with b1 (if you don't need b1 for other things).

If you keep b1, decide if it needs to be text
=Text(a1,"YYYY")
or numeric
=YEAR(a1)


On Oct 14, 11:36*am, wx4usa wrote:
I have a date in cell a1 such as 09/08/09

I have a helper cell b1 that is =Text(a1,"YYYY")

B1 displays 2009

In a cell b8, I have the following =sumproduct((year=b1)*(store=b3)*
(sales)

I get an error. When I simply enter the year into b1 without the text
formula, I get the correct sumproduct results.

What can I do to leave the text formula in? *Excel 2007