title: Excel Formula to count unique values based on three different criteria
body: <p>I am trying to get a row by row count of unique invoices in a spreadsheet. I want excel to do this by reading either 1 for unique or zero for duplicate.
  I have had success with =IF(COUNTIF($C$3:C3,C3)>1,0,1). </p>
  
  <p>This has given me an accurate count based on one specific column, but I have not had any luck advancing this beyond the one column. I would like this formula to be based on three criteria, not just two. </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>  A  B C  D  E  F  G
    Vendor ID Name 1 Invoice Number Inv Date Sum Amount Acctg Date Unique#
    00001  A 0000001  3/16/2015 5.00  5/11/2016 1
    00010  M 0000001  9/14/2015 10.00  5/24/2016 1 
    00010  M 0000001  9/4/2015 15.00  5/24/2016 0
    00005  K 0000285  4/8/2016 20.00  4/18/2016 1
    000106 O 000042  6/7/2016 30.00  6/21/2016 1
    000107 H 006333  4/5/2016 6.00  4/11/2016 1
    000107 H 006333  4/5/2016 6.00  4/12/2016 1​</code></pre>
  </div>
  </div>
  </p>
  
  <p>There are duplicates in all the columns because of how I needed to pull the report. I would like a pull down formula that would give me unique values of A, C, F in a 1,0 format on each row line by comparing each of them against a total combination of each of three columns. Please note vendor M having a duplicate invoice number vs vendor H which has two distinct invoices based on the criteria.</p>
  
  <p>This will be a large drain on resources because of the size of the data. I am looking at around 20-90k lines, but maybe someone can show me a better mousetrap? VBA macro? Match Index? Anyway, onwards to the failures!</p>
  
  <p>Please feel free to explain why they didn't work, or how they could. Also please ignore column locations compared to my example as I was moving things around quite frequently. </p>
  
  <p>=A&amp;C&amp;F then use If(countif('ColumnX')), but this didn't work correctly as I found data that was listed as a repeat when it was actually unique. I think the root problem with doing this was combining the date and general formats into one cell. </p>
  
  <pre><code>=SUMPRODUCT((1/COUNTIFS(E3:E1000,E3:E1000,J3:J1000,J3:J1000,G3:G1000,G3:G1000)))
  </code></pre>
  
  <p>Multiple versions of AND with IF(CountIF)</p>
  
  <p>Multiple versions of =A&amp;C AND CountIF (Date)</p>
  
  <p>I have also looked at the following questions in SE and found them helpful, but ultimately not what I specifically needed, or I failed at implementation. </p>
  
  <p><a href="https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values/21552440#21552440">Simple Pivot Table to Count Unique Values</a>
  I tried this unsuccessfully based on unique invoices, need three criteria not just one.</p>
  
  <p><a href="https://stackoverflow.com/questions/1676068/count-unique-values-in-excel">Count unique values in Excel</a>
  See above.</p>
  
  <p><a href="https://stackoverflow.com/questions/20753066/excel-formula-count-unique-values-in-a-row-based-on-corresponding-value-in-anot">Excel Formula: Count Unique Values in a Row Based on Corresponding Value in Another Row</a>
  This looks like it should work, but I tried and failed to correctly adapt to my problem.</p>
  
  <p><a href="https://stackoverflow.com/questions/36942898/excel-return-count-of-unique-values-based-on-two-columns">Excel - Return Count of Unique Values Based on Two Columns</a>
  This also should work perfectly with addition of third column. Formula yelled at me and called me names. Mentioned something about can't fix stupid.</p>
  
  <p>Please let me know if any parts of the question are unclear. I did my best to not duplicate and trim the information down. Thanks in advance!</p>