Count/frequency of unique values based on TWO criteria sets

[Decoco]

I have a gargantuan list of orders by item for each customer:

Customer A - Item Z - Q1 - Order 1
Customer A - Item Z Q2 - Order 2
Customer A - Item Y - Q5- Order 3
Customer B - Item Z - Q1- Order 4
Customer B - Item W- Q2- Order 5
Customer B - Item W- Q4- Order 6

I want the number of times each customer has ordered each item, said another way: the number of times each item was ordered, by customer.

I feel like maybe I'm halfway there bc I can find the number of times an item was purchased using a formula something like this, for ex (CSE).:

{=SUM(--FREQUENCY(IF(static item range= dynamic item in specific row, order range) order range)>0))}

But the items are not specific to customer so this returns a count/frequency of every order of that item by any customer in the range. I could use this to also get a count of how many times each customer orders, etc. But I do not want the total number of times each customer ordered and I do not want the total number of times each item has been ordered and that information doesn't keep me with a data set so large.

I tried to add an AND function or second IF logic test to specify I want the order number to be specific to BOTH the ITEM AND CUSTOMER but kept getting a: #value . I was thinking of trying a sumif formula to specify that the order range is dependent on the customer. I don't know of that will work yet, I feel like that might be too many arguments.

The size of the data and the precise numbers needed are not really very suitable for a pivot table.

I'm sure there's more than one way to do this. Please help me!

 

[JackDanIce]

Have you tried to use a pivot table?

 

[Aladin Akyurek]

Also, the sample is not very clear. Would you repost it along with the expected results?

 

[Decoco]

Expected answers in "":

Customer A - Item Z - Q1 - Order 1-'"2"
Customer A - Item Z Q2 - Order 2 - "2"
Customer A - Item Y - Q5- Order 3 - "1"
Customer B - Item Z - Q1- Order 4 - "1"
Customer B - Item W- Q2- Order 5- "2"
Customer B - Item W- Q4- Order 6- "2"

 

[Aladin Akyurek]

I suppose Customers (A, B, etc.) are all in one column, Items (Z, Y, W, etc.) in another column, Q values (Q1, Q2, etc.) yet in another column, and Orders (Order 1, Order 2, etc.) also in a column of their own. Is this correct?

 

[Decoco]

I suppose Customers (A, B, etc.) are all in one column, Items (Z, Y, W, etc.) in another column, Q values (Q1, Q2, etc.) yet in another column, and Orders (Order 1, Order 2, etc.) also in a column of their own. Is this correct?

Corect

 

[Aladin Akyurek]

Corect

Row\Col	A​	B​	C​	D​	E​
2​	Customer A	Item Z	Q1	Order 1	2​
3​	Customer A	Item Z	Q2	Order 2	2​
4​	Customer A	Item Y	Q5	Order 3	1​
5​	Customer B	Item Z	Q1	Order 4	1​
6​	Customer B	Item W	Q2	Order 5	2​
7​	Customer B	Item W	Q4	Order 6	2​

In E2 enter and copy down:

=COUNTIFS($A$2:$A$7,$A2,$B$2:$B$7,$B2)