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