If and statement?

[Pritstick]

Hi All,
I need help with a formula. I have many rows of data containing crime numbers in column a, start date in b, end date in c and address in column d. Some of these addresses will be victims more than once. As such, is there any way of finding out where the crime start date and end date is the same and the address is a repeat address? I only want to include those offences that start and end on the same date and also appear more than once on the spreadsheet. I hope that make sense.
Thank you in advance.

 

[Scrappy666]

Hi Pritstick,

In column E, cell E2 and drag down, try:

=countifs($B:$B,$B2,$C:$C,$C2,$D:$D,$D2)

Anything that is over 1 should be duplicate entries that have the same address, start date and end date.
Simply filter on column E to see your results.

I hope that helps!
Dan.

Edit: After re-reading, you may have needed the Start and End date the same date (01/01/2016) and the address duplicated.
If that's the case:

=if(and($B2=$C2,countif($D:$D,$D2)>1),"Duplicate","Non-Duplicate")

Fingers crossed that works.

 

[Pritstick]

Thank you so much, Dan for replying to my post. I will give the formula a go when I get back to work tomorrow.
Thanks again!

 

[Pritstick]

Yes, the start and end dates. You could have crime 1 that occurred between 01/01/16 and 12/01/16 at 1 high street. If another crime occurred at the same address between 12/03/15 and 14/03/15 I want to exclude it from my results but if took place between 1st and 16th Jan 16 again, then I need to include it.
I hope your formula works too!
Thank you.