| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - Pulling info based on lenght of time between dates
I need the formula to search column A:
If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = €œ€ Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between dates
Maybe this:
Entered as an array using the key cobination of CTRL,SHIFT,ENTER (not just ENTER): =IF(SUMPRODUCT(--(A$2:A$5&B$2:B$5=A2&B2))1,IF(C$2:C$5-C2<=30,"Y",""),"") Copy down as needed. I assume that this: AND the dates are within 30 days of each other *Does not* mean +/- 30 days Biff "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between dates
Disregard my reply. After some testing it failed miserably!
I'm having a rough week! Biff "T. Valko" wrote in message ... Maybe this: Entered as an array using the key cobination of CTRL,SHIFT,ENTER (not just ENTER): =IF(SUMPRODUCT(--(A$2:A$5&B$2:B$5=A2&B2))1,IF(C$2:C$5-C2<=30,"Y",""),"") Copy down as needed. I assume that this: AND the dates are within 30 days of each other *Does not* mean +/- 30 days Biff "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between dates
I think we need a more extensive sample set. Will there ever be more than 2
instances of matches? For example: 300531...2,500.00...1/01/2007 300056...8,900.00...1/18/2007 300531...2,500.00...1/31/2007 350031...2,500.00...2/10/2007 There are 3 matches. The first 2 are within 30 days of each other. The last 2 are within 30 days of each other. But, the first and last are greater than 30 days apart. What result would be expected with that sample? Biff "T. Valko" wrote in message ... Disregard my reply. After some testing it failed miserably! I'm having a rough week! Biff "T. Valko" wrote in message ... Maybe this: Entered as an array using the key cobination of CTRL,SHIFT,ENTER (not just ENTER): =IF(SUMPRODUCT(--(A$2:A$5&B$2:B$5=A2&B2))1,IF(C$2:C$5-C2<=30,"Y",""),"") Copy down as needed. I assume that this: AND the dates are within 30 days of each other *Does not* mean +/- 30 days Biff "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between dates
=IF(COUNTIF($A$2:$A$20,A2)1,IF(ABS(C2-MIN(IF((ROW()<ROW($A$2:$A$20))*(A2=$A$2:$A$20)*(B 2=$B$2:$B$20),$C$2:$C$20)))<=30,"Y",""),"")
this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between date
Try
=IF(MIN(IF(A$2:A$5=A2,IF(B$2:B$5=B2,IF(ROW(C$2:C$5 )<ROW(C2),ABS(C$2:C$5-C2),31),31),31))<=30,"Y","N") confirmed with CTRL+SHIFT+ENTER "T. Valko" wrote: I think we need a more extensive sample set. Will there ever be more than 2 instances of matches? For example: 300531...2,500.00...1/01/2007 300056...8,900.00...1/18/2007 300531...2,500.00...1/31/2007 350031...2,500.00...2/10/2007 There are 3 matches. The first 2 are within 30 days of each other. The last 2 are within 30 days of each other. But, the first and last are greater than 30 days apart. What result would be expected with that sample? Biff "T. Valko" wrote in message ... Disregard my reply. After some testing it failed miserably! I'm having a rough week! Biff "T. Valko" wrote in message ... Maybe this: Entered as an array using the key cobination of CTRL,SHIFT,ENTER (not just ENTER): =IF(SUMPRODUCT(--(A$2:A$5&B$2:B$5=A2&B2))1,IF(C$2:C$5-C2<=30,"Y",""),"") Copy down as needed. I assume that this: AND the dates are within 30 days of each other *Does not* mean +/- 30 days Biff "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct - Pulling info based on lenght of time between date
That was the one! I like to think that I'm pretty decent with Excel and
utilizing formulas, but this baby puts me to shame! Thanks for all the help. "Bob Phillips" wrote: =IF(COUNTIF($A$2:$A$20,A2)1,IF(ABS(C2-MIN(IF((ROW()<ROW($A$2:$A$20))*(A2=$A$2:$A$20)*(B 2=$B$2:$B$20),$C$2:$C$20)))<=30,"Y",""),"") this is an array formula, so commit with Ctrl-Shift-Enter, not just Enter. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "watchtower" wrote in message ... I need the formula to search column A: If there are any matches in column A, AND the amounts in column B are the same AND the dates are within 30 days of each other in column C, Result = Y. Otherwise = "" Plan # Amount $ Rcvd. Date Paid Formula Result 300531 2,500.00 1/15/2007 Y 300056 8,900.00 1/18/2007 300531 2,500.00 1/31/2007 Y 350011 4,800.00 1/31/2007 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to select a specific range based on the time value of user form input | New Users to Excel | |||
| varying validation info based on preceding info | Excel Discussion (Misc queries) | |||
| Calculating time increments from dates during working hours | Excel Worksheet Functions | |||
| IF statement for time based data | Excel Worksheet Functions | |||
| update info on 2 sheets @ same time | Excel Worksheet Functions | |||
Linear Mode
