Search code examples
excelexcel-formulauniquecountif

Filter by Unique Value then Count by Additional Criteria

I've got the following columns in my spreadsheet:

URL | Project
-------------
a     1
b     1
c     1
a     2
g     2
x     3

I need to count the unique values in my URL column based on project number. For example, if I'm looking at project "1", there are 2 unique URLs (b and c) because "a" has showed up in previous project (project 2).

I've gotten it to work with pivot tables, but the team I support can't seem to wrap their heads around pivot tables so ideally I'd like a formula where they can just change out the project number and get a count.

Solution

  • Sumproduct seems to give expected results:

    =SUMPRODUCT(--($B$2:$B$7=E4),--(COUNTIF($A$2:$A$7,$A$2:$A$7)=1))

    enter image description here