• 411K Members
• 6,207 Online
• 466K Conversations

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

Occasional Visitor

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

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

 ID Date Transfer Other 12345 5/4/2019 5 12345 5/4/2019 4 12345 5/4/2019 6 98789 6 98789 4 65454 5/4/2019 Y 2 91595 5/4/2019 Y 1 75357 Y 9 75357 Y 1

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

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

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