Sumproduct Date Format Question

  • Thread starter Thread starter wx4usa
  • Start date Start date
W

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!
 
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
 
Hi
Substiture b1 with:

Year(A1)

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

Regards,
Per
 
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)
 
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
 
I have a helper cell b1 that is =Text(a1,"YYYY")
B1 displays 2009
Click to expand...

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