title: Excel 2010 - Count Unique values only in a SUBTOTAL cell
body: <p><strong>Info:</strong>  Excel 2010 (no macros)<br>
  <strong>Data Origns:</strong> MySQL Query / phpMyAdmin <strong>Server:</strong>  Apache<br>
  <strong>Code is run via:</strong> Server SQL Query (copy &amp; paste in the phpMyAdmin) or in MySQL Workbench or using a custom shopping cart manager.<br>
  <strong>Exports to:</strong> Excel (.csv then to .xlsx for sales reports)<br>
  <strong>Notes:</strong> The workbook uses my Query as well as other data  </p>
  
  <p><strong>Question</strong><br>
  I have 2 formulas.  1 that works, 1 that is close but ultimately wrong.<br>
  1)  Gets the total of Unique OrderIDs (in my case 2342 of them) however it won't reduce to a 'SUBTOTAL' when I filter my data.<br>
  <code>=SUMPRODUCT((A8:A30001&lt;&gt;"")/COUNTIF(A8:A30001,A8:A30001&amp;""))</code>  </p>
  
  <p><code>=SUM(IF(FREQUENCY(IF(LEN(A8:A10001)&gt;0,MATCH(A8:A10001,A8:A10001,0),""), IF(LEN(A8:A10001)&gt;0,MATCH(A8:A10001,A8:A10001,0),""))&gt;0,1))</code>  (SHIFT+CTRL+ENTER)  </p>
  
  <p>2) Returns as <code>SUBTOTAL</code> however the count is out by 1 (Returns 8, not 7):<br>
  <code>=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A7,ROW(A7:A10000)-ROW(A7),,1)), IF(A7:A10000&lt;&gt;"",MATCH("~"&amp;A7:A10000,A7:A10000&amp;"",0))),ROW(A7:A10000)-ROW(A7)+1),1))</code>   (SHIFT+CTRL+ENTER)  </p>
  
  <p><code>=SUM(--(FREQUENCY(IF(SUBTOTAL(3,  OFFSET(A7:A10000,  MATCH(ROW(A7:A10000),  ROW(A7:A10000))-1,  0,  1)), COUNTIF(A7:A10000, "&lt;"&amp;A7:A10000), ""), COUNTIF(A7:A10000, "&lt;"&amp;A7:A10000))&gt;0))</code>    (SHIFT+CTRL+ENTER)   </p>
  
  <p><strong>Data</strong><br>
  This has been filtered: Month of Order, Products ID = 7 Units  </p>
  
  <pre><code>OrdID   ProdName    Qty
  2525    test            1
  2538    test            1
  2553    test            1
  2555    test            1
  2556    test            1
  2564    test            1
  2566    test            1 
  </code></pre>
  
  <p>Thanks in advance for your insight.</p>