[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

DateValue function

Folks,
Can the DATEVALUE function reference a cell address such as A3 rather than be hard coded?
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 3
1 Solution
 
byundtCommented:
Of course it can.

What data does the cell contain in your workbook that DATEVALUE cannot convert?
0
 
Frank FreeseAuthor 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
 
byundtCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Frank FreeseAuthor Commented:
One question here....would this be an array formula?
0
 
byundtCommented:
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
 
Frank FreeseAuthor Commented:
I answered my own last question.
Great...two options for one problem.
Thank you very much
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now