Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria with cell references
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 -- Jon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria with cell references
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). -- esbee "Johan" wrote: 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 -- Jon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria with cell references
Why not use something more up to date than the primitive D functions
to sum use =SUMPRODUCT(--(Date_RangeA3),--(Date_Range<B3),Value_Range) to count use =SUMPRODUCT(--(Date_RangeA3),--(Date_Range<B3)) replace the commas by semicolon since you seem to be using a European delimiter -- Regards, Peo Sjoblom "Johan" wrote in message ... 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 -- Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data filter criteria based on cell references? | Excel Worksheet Functions | |||
Displaying multiple cell references in 1 cell | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Changing Cell References To a Different Worksheet in the Same Cell | Excel Worksheet Functions | |||
Transferring cell content between workbooks using cell references | Excel Discussion (Misc queries) |