title: How to count unique values per category in Excel
body: <p>I have looked around on this site and online for an answer to this question, but I have not been sucessful yet so I will ask in a new thread. </p>
  
  <p>I need to be able to count the number of ocurrences of a particular text per category in Excel using a formula (or a number of formulas). Preferably this can be done without the use of VBA, but if it is nessesary I will attempt to write a VBA command.</p>
  
  <p>Here is what I am working with:</p>
  
  <pre><code>Cat.    Word
  A       doo
  A       foo
  A       foo
  A       loo
  B       loo
  B       goo
  B       loo
  A       roo
  A       lol
  B       rar
  C       goo
  ...      ...
  </code></pre>
  
  <p>I would like to have a formula that counts the number of unique occurrences of each word per category. The categories are known and finite, and are included in a list within my excel sheet, but the words can be any amount and are not known. Each category can have words that other categories would have as well the end result should look something like this:</p>
  
  <pre><code>Cat.    Count of distinct ocurrences per category
  A       5
  B       3
  C       1
  </code></pre>
  
  <p>Finally, I know that I can deduplicate the list, but I would like to keep the list intact for archival purposes, so a count that does not require deduping would be ideal.</p>
  
  <p>Here is what I tried to do, but did not work in the end:</p>
  
  <pre><code>=IF(A:A=C1,SUM(IF(FREQUENCY(MATCH(B1:B11,B1:B11,0),MATCH(B1:B11,B1:B11,0))&gt;0,1)))
  </code></pre>
  
  <p>Thank you so much for your help!</p>