COUNTIF - Match amounts and date range

Perhaps its a silly mistake but attached is my excel sheet with Countif formula used.

I'd like to return "MATCHED" in Col D only when Amount in Col C FINDS a Complete & Single but Opposing Match (so -101.50 matched to +101.50 and vice versa only but only one for one match) AND also when date in Col A is within 6 days + or _ of date of the found match.

*actual range of values is in 4000 rows - so its a large table. Col E gives my desired result.

I tried some suggestions/ formula (pasted in Col D) but it doesn't do the date calculations

Any help greatly appreciated!

Highight A1 - C44, select FORMULAS and "Create from selection". Will give you three Named Ranges.

Enter this in D2 and copy down:

=IF(COUNTIFS(Amount,-C2,Date,">="&A2-6,Date,"<="&A2+6)>0,"Matched","")

(If there is a record in the file where the amount is the opposite of the value in this row, AND the date in that Col A is within six days either side of the date in this row, Col D says "Matched", otherwise it is blank.)

Hope this helps

Ochimus

Thanks but this returns "Matched" in D9 also which shouldn't happen - see pic Attachment 711627- as it should only match one pair ?

Also - would this formula work even if sheet is not sorted by dates (ascending or descending)?

Originally Posted by Ochimus

Apologies for not catching the point that you want only one match, and ignore any subsequent ones.
Ochimus

I didnt quite understand what you mean by "but will need an extra section to identify the 'match' to be the row with the date closest to the row being compared" - If I'm guessing right, you mean the clarification to point below?

And to ur 2nd point - it should match the nearest days separated entries but if its equal like 2 days on either side of matched date, then it should match the lower (prior) dates, not the higher date. Hope it clarifies.

Thanks a lot for pitching in!

The following will yield "Matched" in only rows 5:6 as displayed in the Desired Outcome:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

This works with the file attached to post #11 in which the data is sorted in ascending date order.
1. Helper column (you may choose any column and also hide for aesthetic purposes) starting at row 2 and populated using:

Formula:

Please Login or Register  to view this content.

2. Pasted into cell D2 and copied down:

Formula:

Please Login or Register  to view this content.

Note that this identifies two additional matches rows 7:12 and 35:39, which seem to me are correct.
Note that quoting the previous post in not necessary on this forum, a quick reply will work.
Let us know if you have any questions.

JeteMC - thank you so much !

1. so this will need to work only with that helper column or there's a way it can be integrated to the formula (perhaps integrating with your idea and/or Ochimus above) ?

2. What mods do I need to make if I want the formula to just find same sign matches across 6 days apart (so duplicates instead of +ive and -ive sign same amounts)? and

3. does data needs to be sorted ascending order dates first for both above to work?

Yes it will only work with the helper column.
To find same sign matches across 6 days apart modify the helper column formula to read: =AGGREGATE(15,6,ROW(A$1:A1)/(C$1:C1=C2)/(A$1:A1>=A2-6),1)
Yes the data needs to be sorted in ascending order by date.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. To mark the thread as 'Solved' use the thread tools menu above your first post. I hope that you have a blessed day.

JeteMc - You got a great attitude man ! keep it up.
I saw some folks another forum - believe help and obnoxiousness, cant be separated.

BTW - I just saw that I cant send PMs - wud you know the reason for that?

commented for update

Attach a workbook showing the problem … It’s been a while!

...somehow cant delete this post....

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. To mark thread as 'Solved' use thread tools menu above your first post.

Hi- so when working with 2 formlaes that @JeteMc suggested (in post #12, helper column & test column), with good size sample set/rows, formulas seems to be mis-firing right off the bat.

I'm attaching the sheet -it also has desired results for first 130 or so rows, given in Green colored Column. Purpose is really trying to match opposing sign (+ive & -ive) but Same Amount values in Col C, when dates are within 6 days of date values in Col A. SO matches will always come in pairs only.

Here is a revised formula for column E:

Formula:

Please Login or Register  to view this content.

The results in column D are now mostly the same as those in column F.
There are some exceptions such as -74.12 in row 23. The date in A23 is 7/18/2019 and I don't see 74.12 in the date range 7/12/2009 to 7/24/2019.
Let us know if you have any questions.

Thanks - this seems to work much better but I also tested from bottom rows and even though I see ur helper column displaying the matched row- formulae fails to place label in Col D for some rows. I didnt check all but hope I did isolate the issue. here're the rows # 1914, 1883, 1845, 1803, 1780. I've attached the revised sheet also so you'll see it.

And to find same sign matches across 6 days apart, you had suggested helper column formula =AGGREGATE(15,6,ROW(A$1:A1)/(C$1:C1=C2)/(A$1:A1>=A2-6),1) ; how wud the new formula need be adapted for that?

Modify the formula in cells D2 and down to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

While still looking for a way to find same sign matches, I discovered that the opposite sign formula needs modifying:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

The following seems to be finding the same sign matches:
1. In G2 and down: =IF(OR(ISNUMBER(H2),COUNTIFS(H$2:H2,ROW())),"MATCHED","")
2. In H2 and down: =SUM(AGGREGATE(15,6,(ROW(A3:A$1990)-ROW(A2))/(C3:C$1990=C2)/(ISODD(COUNTIFS(C$1:C2,C2,A$1:A2,">="&A2-6)))/(A3:A$1990<=A2+6),1),ROW())
Let us know if you have any questions.