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:
Account ID: a unique ID for each user
State: the mailing state for the account (each account will only have one mailing state)
Order Qty: amount of tickets purchased
| Account ID | State | Order Qty |
|---|---|---|
| 101 | TX | 4 |
| 101 | TX | 6 |
| 102 | NY | 5 |
| 103 | TX | 7 |
| 103 | TX | 9 |
| 104 | CA | 1 |
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:
| State | Unique ID | Order Qty |
|---|---|---|
| TX | 2 | 26 |
| NY | 1 | 5 |
| CA | 1 | 1 |
I tried to merge the state column but that does not calculate the unique ID and Order Qty for the state.
Update:
I tried using group and subtotal and it gave me a very close solution following this tutorial: 
Is there a way to apply the Unique count subtotal to each State group in excel?
CodePudding user response：
You can use COUNTIF for unique IDs and SUMIF for quantities.
=COUNTIF(B:B,"TX")
=SUMIF(B:B,"TX",C:C)
CodePudding user response：
For unique count use:
=SUMPRODUCT((1/COUNTIF($A$2:$A$7,$A$2:$A$7))*($B$2:$B$7=F2))
or if you have O365
=COUNT(UNIQUE(FILTER($A$2:$A$7,$B$2:$B$7=F2,"")))
for quantity:
=SUMIF($B$2:$B$7,F2,$C$2:$C$7)
