Forum Discussion
Count orders which contain a certain product
I have a table with data about orders where the products that are ordered each have a separate row, one row per product. Each order starts with an order number and then the product description. I'm looking to count the number of orders which contain each product. I don't want to know the quantity of each product ordered, just the total number of orders which contain each product. (Apple = 2 orders, etc.). The order numbers repeat for each individual product that is ordered, so I'm having a difficult time finding a way to automate the count of individual orders that contain each product. Counting the number of orders or the total number of each product ordered is simple.
| Order number | Product |
| A100 | Apple |
| A100 | Apple |
| A100 | Banana |
| A101 | Peach |
| A101 | Apple |
| A101 | Apple |
| A102 | Peach |
| A102 | Banana |
| A102 | Banana |
Otherwise (if you don't run Excel 365), load your Orders table to the Data Model (Power Pivot) and setup a Pivot Table from there with Product in Rows, Order in Values and do a Distinct Count on Order field
Sample attached
4 Replies
- LorenzoSilver Contributor
Otherwise (if you don't run Excel 365), load your Orders table to the Data Model (Power Pivot) and setup a Pivot Table from there with Product in Rows, Order in Values and do a Distinct Count on Order field
Sample attached
- Riny_van_EekelenPlatinum Contributor
- KF94607Copper ContributorThat's a great formula! Definitely learned something there. Thanks!