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

Highlighted
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

2 Replies
Highlighted

# 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=""))
Highlighted

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

This is similar to something I'm trying to figure out, but the formula you describe doesn't work for me.

I'm working with content in which I'm trying to get a count of specific types of training but only count the unique sessions.  This is a subset of the data:

 Type of T/TASession Title In Person Presentation Key Financial Management Concepts and Processes In Person Presentation Budget Review Office Hours Budget Review, TA call Live Webinar Best Practices in Budget Development Office Hours Budget Review, TA call Live Webinar GO Certification Steps Live Webinar GO Certification Steps Live Webinar January 2020 Third Thursdays Recorded Instruction January 2020 Third Thursdays Recorded Instruction January 2020 Third Thursdays Instruction only, no demo Manual Holds and Manual Reviews Live Webinar June 2020 Third Thursdays Instruction only, no demo Pre-Award Spending Approval Instruction only, no demo Manual Holds and Manual Reviews

So where the "Office Hours" includes two entries for "Budget Review, TA call", I only want to count the value of one, since the second is a repeated session.  I tried using COUNTIFS but need the ability to automatically filter the duplicates.

Any ideas?