SOLVED

Count orders which contain a certain product

New Contributor

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 numberProduct
A100Apple
A100Apple
A100Banana
A101Peach
A101Apple
A101Apple
A102Peach
A102Banana
A102Banana
4 Replies

@KF94607 In case you are an MS365 subscriber, check this out:

Screenshot 2021-06-18 at 05.52.17.png

File attached.

best response confirmed by KF94607 (New Contributor)
Solution

@KF94607 

 

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

 

Demo.png

Sample attached

 

 

@L z. Thank you. Just learned about the Data Model and that works. Much appreciated.

That's a great formula! Definitely learned something there. Thanks!