SOLVED

New Contributor

# 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
4 Replies

# Re: Count orders which contain a certain product

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

File attached.

best response confirmed by KF94607 (New Contributor)
Solution

# Re: Count orders which contain a certain product

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

# Re: Count orders which contain a certain product

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

# Re: Count orders which contain a certain product

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