| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
DSUM
I have 4 columns in a spreadsheet:
Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DSUM
Try this:
Assume: A2:A20 = Accepted Date B2:B20 = Withdrawn Date C2:C20 = Declined Date D2:D20 = $Value A67 = 01/02/2007 B67 = 14/02/2007 =SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20) Biff "sesler2" wrote in message ... I have 4 columns in a spreadsheet: Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DSUM
In my spreadsheet
D2:D58 = Accepted Date E2:E58 = Withdrawn Date F2:F58 = Declined Date C2:C58 = $Value A67 = 01/02/2007 B67 = 14/02/2007 and this is the formula I typed in : =SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58) Yet it returns 0. I must be doing something wrong here? "T. Valko" wrote: Try this: Assume: A2:A20 = Accepted Date B2:B20 = Withdrawn Date C2:C20 = Declined Date D2:D20 = $Value A67 = 01/02/2007 B67 = 14/02/2007 =SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20) Biff "sesler2" wrote in message ... I have 4 columns in a spreadsheet: Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DSUM
Should work!
Are you sure *all* the dates are true Excel dates? Are the values in $Value numeric numbers and not TEXT numbers? Here's a small sample file that demonstrates this: sesler2.xls 14kb http://cjoint.com/?cwftMd37rp The dates highlighted in yellow fall within your date range. The values highlighted in green are those that are summed. Biff "sesler2" wrote in message ... In my spreadsheet D2:D58 = Accepted Date E2:E58 = Withdrawn Date F2:F58 = Declined Date C2:C58 = $Value A67 = 01/02/2007 B67 = 14/02/2007 and this is the formula I typed in : =SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58) Yet it returns 0. I must be doing something wrong here? "T. Valko" wrote: Try this: Assume: A2:A20 = Accepted Date B2:B20 = Withdrawn Date C2:C20 = Declined Date D2:D20 = $Value A67 = 01/02/2007 B67 = 14/02/2007 =SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20) Biff "sesler2" wrote in message ... I have 4 columns in a spreadsheet: Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DSUM
Thanks T.V. I have no idea what was wrong but I copied your formula in again
and it works perfect. A special thanks for the extra effort of producing the file it was very much appreciated. Your'e a champ! "T. Valko" wrote: Should work! Are you sure *all* the dates are true Excel dates? Are the values in $Value numeric numbers and not TEXT numbers? Here's a small sample file that demonstrates this: sesler2.xls 14kb http://cjoint.com/?cwftMd37rp The dates highlighted in yellow fall within your date range. The values highlighted in green are those that are summed. Biff "sesler2" wrote in message ... In my spreadsheet D2:D58 = Accepted Date E2:E58 = Withdrawn Date F2:F58 = Declined Date C2:C58 = $Value A67 = 01/02/2007 B67 = 14/02/2007 and this is the formula I typed in : =SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58) Yet it returns 0. I must be doing something wrong here? "T. Valko" wrote: Try this: Assume: A2:A20 = Accepted Date B2:B20 = Withdrawn Date C2:C20 = Declined Date D2:D20 = $Value A67 = 01/02/2007 B67 = 14/02/2007 =SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20) Biff "sesler2" wrote in message ... I have 4 columns in a spreadsheet: Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
DSUM
You're welcome. Thanks for the feedback!
Biff "sesler2" wrote in message ... Thanks T.V. I have no idea what was wrong but I copied your formula in again and it works perfect. A special thanks for the extra effort of producing the file it was very much appreciated. Your'e a champ! "T. Valko" wrote: Should work! Are you sure *all* the dates are true Excel dates? Are the values in $Value numeric numbers and not TEXT numbers? Here's a small sample file that demonstrates this: sesler2.xls 14kb http://cjoint.com/?cwftMd37rp The dates highlighted in yellow fall within your date range. The values highlighted in green are those that are summed. Biff "sesler2" wrote in message ... In my spreadsheet D2:D58 = Accepted Date E2:E58 = Withdrawn Date F2:F58 = Declined Date C2:C58 = $Value A67 = 01/02/2007 B67 = 14/02/2007 and this is the formula I typed in : =SUMPRODUCT(--(MMULT((D2:F58=A67)*(D2:F58<=B67),{1;1;1})0),C2: C58) Yet it returns 0. I must be doing something wrong here? "T. Valko" wrote: Try this: Assume: A2:A20 = Accepted Date B2:B20 = Withdrawn Date C2:C20 = Declined Date D2:D20 = $Value A67 = 01/02/2007 B67 = 14/02/2007 =SUMPRODUCT(--(MMULT((A2:C20=A67)*(A2:C20<=B67),{1;1;1})0),D2: D20) Biff "sesler2" wrote in message ... I have 4 columns in a spreadsheet: Accepted Date, Withdrawn Date, Declined Date, $Value. In 2 cells I have start and end dates. i.e. a67 = 01/02/2007 and b67 = 14/02/2007. I would like to sum the $Value column if: (Accepted Date is = a67 and <= b67) or (Withdrawn Date is = a67 and <= b67) or (Declined Date is = a67 and <= b67). I have tried to no success so would greatly appreciate some help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using DSUM | Excel Worksheet Functions | |||
| DSUM | Excel Worksheet Functions | |||
| Sum If or DSUM help? | Excel Worksheet Functions | |||
| dsum | New Users to Excel | |||
| DSUM | Excel Worksheet Functions | |||
Hybrid Mode
