# DateValue function

Folks,
Can the DATEVALUE function reference a cell address such as A3 rather than be hard coded?
###### Who is Participating?

x

Commented:
Your problem is with Range("C2"). This is VBA syntax, and does not work in a worksheet formula. The worksheet equivalent is INDIRECT("C2").

What I suggest instead is a regular formula using SUMPRODUCT:
=SUMPRODUCT((C2=\$A\$3:\$A\$40)*(D2=\$B\$3:\$B\$40))     if C2 is a date formatted like you showed
=SUMPRODUCT((DATEVALUE(C2)=\$A\$3:\$A\$40)*(D2=\$B\$3:\$B\$40))        if C2 is text
0

Commented:
Of course it can.

What data does the cell contain in your workbook that DATEVALUE cannot convert?
0

Author Commented:
Let's say A3:A40 are dates: 11/01/2013 - 11/30/2013
In cell C2 I would like to enter in a date: 11/15/2013
In cells B3:B40 are various product numbers many repeated for some products might have more sales during this time period.
In cell D2 I enter in a product number: K7896
F2 will tell me how many of a product I sold in that day.
I tried to do this in F2:
{=SUM((DATEVALUE(Range("C2"))=\$A\$3:\$A\$40)*(Range("D2")=\$B\$3:\$B\$40)}

I get a #VALUE error in F2

I know I could use filters but I'm trying to do this with a formula.
0

Author Commented:
One question here....would this be an array formula?
0

Commented:
The SUMPRODUCT formula is an array formula that does not need to be array-entered.

If the formula is not working for you, could you please post a sample workbook that demonstrates the problem? It doesn't need more than five rows of data.
0

Author Commented:
I answered my own last question.
Great...two options for one problem.
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.