Excel - formula counting the number of distinct values in column, without taking into account the indicated values from another column

0
votes

I have following data in excel:

   Category           ID

      a            100-1234

      a            100-12345

      b            100-123456

      b            110-1234

      b            110-12345

      c            1120-92029

      c            12303-223

      c            12039-10300

      c            12930-20102

I would like to write a formula to calculate the number of distinct/different IDs of all categories except Category = "c" (without breakdown by category, just a total number of distinct IDs).

2
excelexcel-formuladistinct-values
except Category = "c" means what. Only count distinct for a nd b? Then what would be your output? – Harun24HR
Yes, I want to get a total number of distinct IDs for a and b categories – Agnieszka
If you have Excel 365, use FILTER get get all qualifying records, the UNIQUE, then COUNT – chris neilsen

2 Answers

0
votes

Assuming that the Category column is in column A, put this in your C2:

=IF(A2="c",0,IF(COUNTIF($B$2:B2,B2)>1,0,1))

Drag it to the end of the list. You can then sum the column and you should have your result.

0
votes

To count unique IDs excluding c Total Count use below formula-

=SUMPRODUCT(($A$2:$A$10<>"c")/COUNTIF($B$2:$B$10,$B$2:$B$10&""))

To count by category use (As per screenshot) in E4-

=SUMPRODUCT(($A$2:$A$10=D4)/COUNTIF($B$2:$B$10,$B$2:$B$10&""))

Then drag and down for next one.

enter image description here