Sumproduct - Pulling info based on lenght of time between dates

[Guest]

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

 

[T. Valko]

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

 

[T. Valko]

Disregard my reply. After some testing it failed miserably!

I'm having a rough week!

Biff

 

[T. Valko]

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

 

[Bob Phillips]

=IF(COUNTIF($A$2:$A$20,A2)>1,IF(ABS(C2-MIN(IF((ROW()<>ROW($A$2:$A$20))*(A2=$A$2:$A$20)*(B2=$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)

 

[Guest]

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

 

[Guest]

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.