SUMPRODUCT with multiple items + double existing values

Hi,

I am analyzing room departures and the corresponding email capture from my system generated report. Usually, there should be one guest per room and it should be listed on my report as one checkout. Depending on if that person has an email address on file, I can calculate my email capture. Now the situation is that there are for example 3 person staying in a room, shown on the report as 3 departures of which we capture one email address, therefore the score shows 33% email capture. This is not correct because as long as I can get one email per room its already a full capture for me.

Currently I am using this formula to capture the checkouts with email in a specific month:

Please Login or Register  to view this content.

I somehow need excel to check if the arrival date + room number is the same, and if yes calculate the result as ONE room departure, then check if either of the records has an email address on file.

Any ideas on how I can do this?
Thanks,
A2k

Room Departure Sample.xlsx

Thanks alot micope, this is awesome stuff. Not a fan of the helper column though, but it works flawlessly either way