adityakapoor
New Member
Hello,
I am struggling to manage something with a pivot table (which I could do with multiple columns and COUNTIFS functions.
I am looking at sales data
I would like to understand the frequency of ordering of my entire customer base. So in the pivot table I can calculate the count of orders by customer (using customer in the rows and count of order #) in the sum column.
Now I have a list of all the customers and the number of times they have placed an order.
What I would like to end up with is a list which shows how many customers ordered 1-3 items, how many 4-6 times etc.
So its a count of a count (am I making sense?)
I managed this without pivot tables by using the unique function to get a list of the customers.
Then used a countifs function to calculate the number of orders in a specific period of time by that customer.
Then I used a helper column where an IFS function helped me label the customer as 1 to 3, 4 to 6, 7 to 9 and so on based on the count value
Then I made a column with the brackets listed (i.e. 1 to 3, 4 to 6, etc)
Finally another column to do a quick COUNTIF (for the list of rows with 1 to 3, 4 to 6, ETC)
Problem is that we have no clue how many customers will be there in the list. So I have dragged the formula down to row 500, but as the data range expands, this might not suffice. So surely there is value in using a pivot table. I can easily get a list of the customers and the number of times they have ordered. How do I then group that into number of customers who have ordered 1 to 3 times or 4 to 6 times, etc?
I am struggling to manage something with a pivot table (which I could do with multiple columns and COUNTIFS functions.
I am looking at sales data
I would like to understand the frequency of ordering of my entire customer base. So in the pivot table I can calculate the count of orders by customer (using customer in the rows and count of order #) in the sum column.
Now I have a list of all the customers and the number of times they have placed an order.
What I would like to end up with is a list which shows how many customers ordered 1-3 items, how many 4-6 times etc.
So its a count of a count (am I making sense?)
I managed this without pivot tables by using the unique function to get a list of the customers.
Then used a countifs function to calculate the number of orders in a specific period of time by that customer.
Then I used a helper column where an IFS function helped me label the customer as 1 to 3, 4 to 6, 7 to 9 and so on based on the count value
Then I made a column with the brackets listed (i.e. 1 to 3, 4 to 6, etc)
Finally another column to do a quick COUNTIF (for the list of rows with 1 to 3, 4 to 6, ETC)
Problem is that we have no clue how many customers will be there in the list. So I have dragged the formula down to row 500, but as the data range expands, this might not suffice. So surely there is value in using a pivot table. I can easily get a list of the customers and the number of times they have ordered. How do I then group that into number of customers who have ordered 1 to 3 times or 4 to 6 times, etc?