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!
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!
