|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com