title: COUNTIFS in a spreadsheet while ignoring duplicates in a separate column
body: <p>I'd like to find how to use the COUNTIFS function in excel to count the rows in a spreadsheet while ignoring duplicate values in a separate column. Here is a sample of the data:</p>
  
  <p>      [<img src="https://i.stack.imgur.com/mE94E.png" alt="Sample data in pastable format below">]</p>
  
  <p>I would like to count the number of rows that are "One" on status and "Blue" on category. However, I only want to count the duplicates for these respective values in "ID" once. </p>
  
  <p>Here's what I've tried:</p>
  
  <pre><code>=SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)&gt;0,1))
  </code></pre>
  
  <p>Same problem as the previous example. - this works for giving me the number of duplicates in column A, but I can't work any IF statements in effectively.</p>
  
  <pre><code>=IF(B2:B12="One",IF(C2:C12="Blue",SUM(IF(FREQUENCY($A$2:$A$12,$A$2:$A$12)&gt;0,1))))
  </code></pre>
  
  <p>When I add IF statements in, I get the same number as the earlier formula.</p>
  
  <p>For your copy/pasting pleasure:</p>
  
  <pre><code>ID     Status  Category
  1423    One     Blue
  1423    One     Blue
  1423    One     Red
  5124    One     Blue
  5124    One     Blue
  2341    Two     Blue
  1111    One     Red
  1212    Two     Red
  1212    One     Blue
  1111    One     Red
  5124    One     Red
  </code></pre>
  
  <p>The ideal result would be <strong>3</strong>.</p>