In all the help for the database functions, the criteria always show the
values hard coded with a value like:

="=10"

I want to have a sum of a certain column where the date is within 2 dates I
have derived (using formulas) in two cells outside the database and criteria
range.

For example, I have a database in A1:D10 and the criteria is in F1:H2.
Column A is a column of numbers and column B is a column of dates. I have a
begin date calculated in J2 and an end date calculated in K2. Column F
matches column A and columns G and H both match column B so that I can do
multiple criteria on column B. How do I set the values of G2 and H2 so that
it basically says:

in G2 - Date in column B is = J2
in H2 - Date in column B is also <= K2

I've tried in G2 - ="=J2"
I've tried in H2 - ="<=K2"

My DSUM looks like ("Score" is column A):
=DSUM(A1:D10,"Score",F1:H2) but I get the error:

A values in this formula is of the wrong data type

I looked at the help for that error but I don't see what I'm doing wrong.
It doesn't seem like Excel will allow a cell reference in the Criteria
section. All the help examples are hard coded. How do I get a criteria to
refer to a cell's value instead of hard coding the criteria values?

Thanks,
George

Try

in G2 - ="=" & J2
in H2 - ="<=" & K2


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=493129

Having the cell reference inside the quotes will keep Excel from even
recognizing it as a reference. Try ="=" & j2. (You'll probably notice that
the dates turn into numbers in the 30000 range; that's just Excel's internal
representation of a date).

"George Davis" wrote:

In all the help for the database functions, the criteria always show the
values hard coded with a value like:

="=10"

I want to have a sum of a certain column where the date is within 2 dates I
have derived (using formulas) in two cells outside the database and criteria
range.

For example, I have a database in A1:D10 and the criteria is in F1:H2.
Column A is a column of numbers and column B is a column of dates. I have a
begin date calculated in J2 and an end date calculated in K2. Column F
matches column A and columns G and H both match column B so that I can do
multiple criteria on column B. How do I set the values of G2 and H2 so that
it basically says:

in G2 - Date in column B is = J2
in H2 - Date in column B is also <= K2

I've tried in G2 - ="=J2"
I've tried in H2 - ="<=K2"

My DSUM looks like ("Score" is column A):
=DSUM(A1:D10,"Score",F1:H2) but I get the error:

A values in this formula is of the wrong data type

I looked at the help for that error but I don't see what I'm doing wrong.
It doesn't seem like Excel will allow a cell reference in the Criteria
section. All the help examples are hard coded. How do I get a criteria to
refer to a cell's value instead of hard coding the criteria values?

Thanks,
George

That did it! Thank you so much!

George

"blatham" wrote:


Try

in G2 - ="=" & J2
in H2 - ="<=" & K2


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=493129

No probs

you can do

="=" & TEXT(J2,"dd/mm/yy")

to change the format of the date


--
blatham
------------------------------------------------------------------------
blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
View this thread: http://www.excelforum.com/showthread...hreadid=493129