Help with formula to find duplicates within 30 days and populate data if true.

I am having an issue figuring out the a formula that will look for duplicate stock symbols as they are entered BUT are no more than 30 days old AND the previous transaction was sold at a loss. (Of course, I need to apply this to open entries already in the spreadsheet.)

If this criteria is true, then (ideally) I'd like to have the following happen:
1. Cell next to Symbol in "Wash Sale?" column set to "Yes"
2. The Loss from the previous sale of the stock in Column X to be populated as a positive number in Column D of the current stock purchase.
3. Current Symbol highlights (I should be able to figure this out with Conditional Formatting)
4. The price / share in Column K will update based on the current purchase price plus number of shares purchased divided by the carried over loss in Column D

I am attaching an extract of my current spreadsheet. You will see two stocks (UPC & MBRX) for which I am trying to accomplish these goals. I've also added "Notes" to the cells where I'm trying to populate the data.
There are two other sets of symbols in the attached spreadsheet that shouldn't be affected by this formula (RAIL & WIMI).
RAIL was sold for a profit the first time so no loss to carry over
WIMI was split when trying to purchase 100 shares and neither entry has a SOLD date.

I thought using the COUNTIFS function would work but I'm stumbling with the criteria of <30 Days from the date in the "A" column.

If someone can help me out with this, or let me know what functions I need to use, it would be greatly appreciated.

Hi,
How about that, in C8 and down:

=IFERROR(IF(([@[Date Bought]]-INDEX($A7:A$8,MATCH([@Symbol],$B7:B$8,0))<30)*(INDEX($X$8:$X$25,MATCH([@Symbol],$B7:B$8,0))<0)>0,"Yes",""),"")

WOW! Thank you so much belinda! It looks like that's working perfectly. I will have to study your formula so I understand what is happening. I don't think I've ever seen "IFERROR" used before.

Can you look at the next column for the two scenarios below? (of course, these are based on the fact that the value in Column C = "Yes"

1. If there is only one instance of a sale and loss within the 30 days, I'd like to place the Loss from the previous sale (Column X) into Column D of the current sale (or latest in this case).
EXAMPLE
UPC - since only 1 other transaction within 30 days which resulted in a loss (X8), in the 2nd transaction for UPC in Column D the value would be 71.75 (pulled from X8 on the previous transaction)
2. If there is more than one sale for a loss within the last 30 days, then the formula would add the loss in Col D from the previous sale and the Loss from that sale to Col D of the current transaction.
EXAMPLE
MBRX - First transaction = loss
2nd Transaction - pull loss from 1st transaction within 30 days (X12) and place in D14.
3rd Transaction - If within 30 days of 2nd Transaction AND 2nd transaction = loss then add value in D14 + X14 and place in D16.

Really? you've never used IFERROR in your life?
I have to ask how old are you...

I will look at the 2 other requests later today.

Hi
As I promised - try this in D8 and down:

=IF(COUNTIFS($B$6:B8,B8,$X$6:X8,"<0")>1,SUMPRODUCT((($B7:B$8=B8)*($X7:X$8<0)*($X7:X$8))),"")

Hope this works for you...

Originally Posted by belinda200

Really? you've never used IFERROR in your life?
I have to ask how old are you...

I will look at the 2 other requests later today.

Thanks belinda! I will take a look at this sometime this week.

Not sure it has anything to do with age but maybe how much someone uses Excel, not to mention create formulas.

To answer your question though, I like to say "I'm old enough to know better but young enough not to care."

Thanks again for your help! It's really appreciated!

You're welcome : )