Counting dates before a specific date and event

Hello,

I?m wondering if someone might be able to help.
I have 2 datasets in a large sheet I?m working in. The first sheet lists all the emails sent out to customers (1 row per email), the other sheet lists all purchases made (1 row per purchase).

What I am trying to do, is count the number of emails that were sent to a person, prior to them making a purchase. However, whilst 1 person may have received 2 emails, and both emails were sent before their 2 purchases, if 1 email was sent before purchase 1, and the other email sent before purchase 2, I want to record this as the person having received only 1 email before each separate purchase?.not sure if I?ve explained this very well, but hopefully my attached example shows this better, and uses actual dates from my data, and the order in which they appear.

Column G shows the results I?m trying to achieve with a formula?..if this is possible.

Thanks in advance if anyone has taken the time to read this !

Hi Bernie ,

Thanks so much for your time and taking a look at this. The formula worked perfectly in the sample data, but isn't working in my actual data....I will need to try and work out what's going on, as I set the sample data up very similar to my actual data.

Hi HansDouwe,

Thank you for your reply. Whilst I do understand where you are coming from, this is the logic I need to work with in my data, and so the expected results I put in the sample data, is the results I would need to achieve.

Hi Bernie,

strangely, when I did try and copy out actual data (stripping our sensitive info), the formula worked just fine ! So I can't even seem to be able to replicate the issue I'm having in my "live" data set in an example I've tried it again a number of times, but I keep getting 0 as a result in my data. The only difference is that in my "live" data I have many more columns and the data is held on 2 different tabs, rather than just one.

Think I may have to rethink how I do this (but I can see that your formula does do the job I needed it to do though !)