Sumproduct Date Format Question

[wx4usa]

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

Thanks a bunch everyone!

 

[Trevor Shuttleworth]

What is in cell B3? What are the ranges Year, Store and Sales and what data
do they contain? A few samples would help.

Regards

 

[Per Jessen]

Hi
Substiture b1 with:

Year(A1)

=SUMPRODUCT(--(Year=YEAR(A1)),--(Store=B3),SALES)

Regards,
Per

 

[wx4usa]

Hi
Substiture b1 with:

Year(A1)

=SUMPRODUCT(--(Year=YEAR(A1)),--(Store=B3),SALES)

Regards,
Per

Very good...that works great Per Jessen!!!

 

[zvkmpw]

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)

 

[Bob Umlas]

change year=b1 to year=b1*1
or change the cell in B1 to read =YEAR(A1) so it's still numeric
Bob Umlas
Excel MVP

 

[T. Valko]

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

B1 displays 2009

The result of the TEXT formula is a *text* value even though it looks like
the number 2009. So, you'd have to convert the text value 2009 to the
numeric value 2009.

One way to do that is like this:

=--Text(a1,"YYYY")