Count/frequency of unique values based on TWO criteria sets

Decoco

New Member
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.

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JackDanIce

Well-known Member
Have you tried to use a pivot table?

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

Decoco

New Member

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"

MrExcel MVP

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

New Member
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

MrExcel MVP

 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)

Replies
6
Views
131
Replies
3
Views
60
Replies
13
Views
398
Replies
1
Views
110
Replies
3
Views
86