Home

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

Highlighted
Occasional Visitor

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

 

IDDateTransferOther 
123455/4/2019 5
123455/4/2019 4
123455/4/2019 6
98789  6
98789  4
654545/4/2019Y2
915955/4/2019Y1
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:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)*
(B2:B10<>"")*
(C2:C10=""))