Forum Discussion
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
- You can use this formula:
=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10)*
(B2:B10<>"")*
(C2:C10=""))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?
