title: Excel formula to count duplicates as one
body: <p>I am trying to create a formula which will count all rows (duplicates as one) based on a value in another column.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column A</th>
  <th>Column B.</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>p1</td>
  <td>Joe Bloggs</td>
  </tr>
  <tr>
  <td>p2</td>
  <td>Bob Bloggs</td>
  </tr>
  <tr>
  <td>p1</td>
  <td>Joe Bloggs</td>
  </tr>
  <tr>
  <td>p3</td>
  <td>John Bloggs</td>
  </tr>
  <tr>
  <td>p1</td>
  <td>Mary Bloggs</td>
  </tr>
  <tr>
  <td>p1</td>
  <td>Alan Bloggs</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to count the number of rows where the value in column A === p1. So on the above running</p>
  <p><code>=COUNTIF(A1:A20,&quot;p1&quot;)</code> will return 4, but as Joe Bloggs is p1 twice I only want to count Joe Bloggs once.</p>
  <p>Running <code>=SUMPRODUCT((B1:B15&lt;&gt;&quot;&quot;)/COUNTIF(B1:B15,B1:B15&amp;&quot;&quot;))</code> will count duplicates as one, this would return 5 people as it would only count Joe Bloggs once.</p>
  <p>Is it possible to combine the <code>COUNTIF</code> and <code>SUMPRODUCT</code>.</p>
  <p>Something else I tried was adding this forumla in Column C, <code>=IF(COUNTIFS(A$1:A3,A3,B$1:B3,B3)&gt;1, COUNTIFS(A$1:A3,A3,B$1:B3,B3)-1,&quot;&quot;)</code> this would count the duplicates and then I can run <code>=COUNTIF(Sheet1!A1:A20,&quot;p1&quot;)-COUNTIF(Sheet1!C1:C7,&quot;&gt;0&quot;)</code> but if <code>p2</code> or <code>p3</code> had duplicates then this would not generate the correct result.</p>
  <p>So basically I am trying to say, <code>IF Column A is p1 then count all p1 rows but IF column B has duplicates then only count the first instance (counting the duplicates as 1)</code></p>