Criteria with cell references

[Guest]

I have a table with data named "DataB":
A B C
1 Date Descr Value
2 2007-09-04 Alan 53,51
3 2007-09-06 Alan 61,66
4 2007-09-09 Alan 91,48
5 2007-09-13 Gere 60,34
6 2007-09-18 Gere 45,57
7 2007-09-20 Gere 48,29

I would like to make a summary similar to the following:
A B C D
E
1 Summary
2 From To Week Sum
Count
3 2007-09-03 2007-09-09 36 =DSUM(DataB;3;">A3&<B3")
=DCOUNT(DataB;3;">A3&<B3")
4 2007-09-10 2007-09-16 37 =DSUM(DataB;3;">A4&<B4")
=DCOUNT(DataB;3;">A4&<B4")

Where of-cource the D-functions shown as text doesn't work. Is it possible
to have criterias with cell-references to the same row as the function itself
resides on? I would be glad if it is possible to solve the problem using
D-functions, since then it also works with WM5-machines. It can of-cource be
solved using array-formulas, but they cannot be transfeered to a WM5-machine

 

[Guest]

Johan,
Criteria in DSUM function refers to the cells containing the conditions.
You cannot as far as I understand ,put the conditions themselves in the
formula directly.

You have the dates at A3 and B3 which shall form part of the criteria.
The criteria range should have column labels. In this case it would appear
like this say at A30 : B31

A B
30 Date Date
31 =">"&A3 ="<"&B3

the function will be:
=DSUM(DataB,3,$A$30:$B$31)

DCOUNT will be similar.

I find that you have used semicolons inside the functions. They should be
"," ( commas).

 

[Peo Sjoblom]

Why not use something more up to date than the primitive D functions

to sum use

=SUMPRODUCT(--(Date_Range>A3),--(Date_Range<B3),Value_Range)


to count use

=SUMPRODUCT(--(Date_Range>A3),--(Date_Range<B3))

replace the commas by semicolon since you seem to be using a European
delimiter


--


Regards,


Peo Sjoblom