Count orders which contain a certain product

[KF94607]

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

[#2]

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

File attached.

[#3 · Accepted 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

Sample attached

[#4]

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

[#5]

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