Clever formula to count unique instances of two columns?

[RockandGrohl]

Morning everyone. In excel I have a dataset which has multiple rows per projectID.

Column A is ProjectID, and column E is Version, which holds information on what version of the ProjectID it is. For example, there can be 30 lines for Project 12345, which only has Version 3, so the result I want is "1" because there's only one combination of Project 12345 with Version 3.

If there's another ProjectID such as Project 09876 with 20 lines for Version 3, and then another 20 lines for Project 09876 for Version 5, then I'd like the formula to result in "2" for each line of Project 09876 because there are two valid combinations of ProjectID and Version.

What formula can I use for this? I'm thinking of some clever combination of Countifs etc.


This table is being imported into Power BI so if there's a better way to do this with a measure, I'm all ears. I just thought I'd do it in Excel for familiarity

 

[RockandGrohl]

Count unique values in a range with COUNTIF

To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in F6 is: =SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE.

exceljet.net

I'm sort of looking for something like this but where I can countifs, so I can say for each project ID, how many unique Versions are there?

 

[RockandGrohl]

=IFERROR(ROWS(UNIQUE(FILTER($E$2:$E$10000,$A$2:$A$10000=$A2))),0)

This is what I was after. Seems to work nicely.