Home

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

jupstin
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=""))
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies