I need help with a formula

Occasional Visitor

Hey, well, I have trouble finding the formula for the next goal: First I have an OrderID (i work at a sushi restaurant) for every especific order and a phone number from every specific client, wich is the same for every product in said order. This means that if a client orders 3 rolls at the same time, this three products will share the OrderID and the phone number. Now, I need to count every different order from the same client to know how many times they order sushi. This is difficult because there isn't an unique ID for one order from one client that doesn't repit itself with each product on the order.

I tried using the date, but some clients orders multiple times on the same day; I tried with the hour, but if this client asks for multiple products in different times on the day, all the products in the first order will share the first hour and so on; I tried with concatenate using the OrderID and the ProductID (an specific ID for each product), but if a client orders two items of the same product at the same time, there isn't any difference between them.

Now, I was told that the function SUME.IFS would help me, but until now I've been unable to solve this. So, if someone has any idea, I'll be very thankfull for the input:(


(english isn't my mother tongue, so excuse me if you find grammatical errors)

1 Reply


Do you have Microsoft 365 or Office 2021? If so, you can use FILTER, UNIQUE and COUNTA - see the attached demo workbook.