title: Excel: how to count unique ID within each state as well as the tickets purchased within each state
body: <p>I'm completely new to the excel world, and I am currently working on this very specific problem. Assume I'm doing statistics for a ticketing platform. Here is the data I was given:</p>
  <ul>
  <li><p><strong>Account ID</strong>: a unique ID for each user</p>
  </li>
  <li><p><strong>State</strong>: the mailing state for the account (each account will only have one mailing state)</p>
  </li>
  <li><p><strong>Order Qty</strong>: amount of tickets purchased</p>
  </li>
  </ul>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Account ID</th>
  <th>State</th>
  <th>Order Qty</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101</td>
  <td>TX</td>
  <td>4</td>
  </tr>
  <tr>
  <td>101</td>
  <td>TX</td>
  <td>6</td>
  </tr>
  <tr>
  <td>102</td>
  <td>NY</td>
  <td>5</td>
  </tr>
  <tr>
  <td>103</td>
  <td>TX</td>
  <td>7</td>
  </tr>
  <tr>
  <td>103</td>
  <td>TX</td>
  <td>9</td>
  </tr>
  <tr>
  <td>104</td>
  <td>CA</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like to create a table that dynamically count the unique IDs by state, as well as the total number of tickets bought for each state. The table will look something like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>State</th>
  <th>Unique ID</th>
  <th>Order Qty</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>TX</td>
  <td>2</td>
  <td>26</td>
  </tr>
  <tr>
  <td>NY</td>
  <td>1</td>
  <td>5</td>
  </tr>
  <tr>
  <td>CA</td>
  <td>1</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried to merge the state column but that does not calculate the unique ID and Order Qty for the state.</p>
  <p><strong>Update</strong>:
  I tried using group and subtotal and it gave me a very close solution following this tutorial: <a href="https://www.youtube.com/watch?v=oFdhANcS6VA" rel="nofollow noreferrer">https://www.youtube.com/watch?v=oFdhANcS6VA</a></p>
  <p>But there is one thing missing, which is the Account ID unique count, because it seems like Excel does not have a built in Count(unique) like apple numbers does:
  <a href="https://i.stack.imgur.com/WHxEl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WHxEl.png" alt="enter image description here" /></a></p>
  <p>Is there a way to apply the Unique count subtotal to each State group in excel?</p>