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!

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


"wx4usa" wrote in message
...
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!

Hi
Substiture b1 with:

Year(A1)

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

Regards,
Per

"wx4usa" skrev i meddelelsen
...
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!

On Oct 14, 3:00*pm, "Per Jessen" wrote:
Hi
Substiture b1 with:

Year(A1)

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

Regards,
Per

"wx4usa" skrev i ...

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!

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

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

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

"wx4usa" wrote in message
...
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!

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")

--
Biff
Microsoft Excel MVP


"wx4usa" wrote in message
...
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!