Formula for counting duplicate rows only once combined with multiple criteria.

Occasional Visitor

Here's what I'm trying to do. I have a worksheet like this:


123455/4/2019 5
123455/4/2019 4
123455/4/2019 6
98789  6
98789  4
75357 Y9
75357 Y1


What I would like is a formula to count every ID one time that has a value in the Date column and no value in the Transfer column. All rows will always have an ID and at least some other data.


My problem is that I can get a count of all rows what have a value in Date and no value in Transfer, but it includes duplicate ID entries, which I don't want.


With the table above, I would like to return the value "1" because ID 12345 represents 1 person, but I can only manage to get the value "3" because ID 12345 is repeated 3 times.


I hope that makes sense. I know I could get the answer I'm looking for by removing duplicates, but I'd like to avoid that if possible. Thanks

1 Reply
You can use this formula: