excel-formulacountcountif

Counting sets of values in a column associated with another columns values


I am asking for a solution to this scenario in Excel. I want to get a count of sets of unique values in one column that are associated to values in another column.

Example:

A B C
1 a 3
1 a 3
2 a 3
2 a 3
3 a 3
4 b 1

In column A there are 4 unique values in the data set. Duplicates that extend from data links are unavoidable in this scenario. I would like a formula in Excel that will show the number of sets associated with the values in column B. The results would match the data in column C where the formulas would be placed. 3 different sets are associated with value "a" in column B. And 1 set is associated with value "b" in column b.

I was trying to utilize a formula of =COUNTIFS() but I was having trouble separating the unique sets that were connected to the unique values in column B. I am not sure if a dummy column would help for a combination of formulas. Any help or suggestions would be appreciated! this data set is being scrapped into Excel for analysis. Pulling out column A to pick remove duplicates is not ideal. Occurrences must be in column C for transfer to Tableau visual tool mapping.


Solution

  • You can use FILTER and UNIQUE.

    I assume your data is in range A1:B6, you can use below formula and drag down:

    =COUNT(UNIQUE(FILTER($A$1:$A$6,$B$1:$B$6=B1,""),FALSE,FALSE))

    enter image description here