Count/frequency of unique values based on TWO criteria sets

Decoco

New Member
Joined
May 31, 2018
Messages
4
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!
 

Some videos you may like

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
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
Have you tried to use a pivot table?
 

Decoco

New Member
Joined
May 31, 2018
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

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
Joined
May 31, 2018
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

Row\Col
A​
B​
C​
D​
E​
2​
Customer AItem ZQ1Order 1
2​
3​
Customer AItem ZQ2Order 2
2​
4​
Customer AItem YQ5Order 3
1​
5​
Customer BItem ZQ1Order 4
1​
6​
Customer BItem WQ2Order 5
2​
7​
Customer BItem WQ4Order 6
2​

In E2 enter and copy down:

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

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,238
Members
409,857
Latest member
KailuaTown
Top