2 categories
 

I am trying breakdown a spreadsheet of orders for business for the month of
october. I pulled data from my proprietary system and created an excel
spreadsheet. I have only two columns one is the customer name, and the other
is the state that the order originated. My customers place orders for
different states so for each order the customers name is listed and the state
they placed the order for is listed. I have it sorted by customer and then
by state, so what I would like to do is count the amount of orders per state
by each customer. For instance I have a customer that has placed 10 orders
for CA, 10 orders for NV, and ten orders for AZ. The excel spreadsheet lists
this customers name 30 times 10 for each state. But I have a fairly large
spreadsheet and don't wish to count how many orders this customer placed for
each state. Is there a formula to automate this with. I am sorry that was
pretty long winded. Please advise

2 categories
 

I think a pivot table would satisfy your request.

Data.Pivot Table
Select Excel List
Select your data list
Layout:
€¢Set the rows to be Customer and State
€¢Set the data to be COUNT OF STATE
Select a cell to locate the Pivot Table on
Finish

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Joe Resler" wrote:

I am trying breakdown a spreadsheet of orders for business for the month of
october. I pulled data from my proprietary system and created an excel
spreadsheet. I have only two columns one is the customer name, and the other
is the state that the order originated. My customers place orders for
different states so for each order the customers name is listed and the state
they placed the order for is listed. I have it sorted by customer and then
by state, so what I would like to do is count the amount of orders per state
by each customer. For instance I have a customer that has placed 10 orders
for CA, 10 orders for NV, and ten orders for AZ. The excel spreadsheet lists
this customers name 30 times 10 for each state. But I have a fairly large
spreadsheet and don't wish to count how many orders this customer placed for
each state. Is there a formula to automate this with. I am sorry that was
pretty long winded. Please advise

2 categories
 

I am having a little difficulty using the pivot table. I was able to do a
count of all orders by customer. But I have not found where to do a count by
state. Any ideas?

"Ron Coderre" wrote:

I think a pivot table would satisfy your request.

Data.Pivot Table
Select Excel List
Select your data list
Layout:
€¢Set the rows to be Customer and State
€¢Set the data to be COUNT OF STATE
Select a cell to locate the Pivot Table on
Finish

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Joe Resler" wrote:

I am trying breakdown a spreadsheet of orders for business for the month of
october. I pulled data from my proprietary system and created an excel
spreadsheet. I have only two columns one is the customer name, and the other
is the state that the order originated. My customers place orders for
different states so for each order the customers name is listed and the state
they placed the order for is listed. I have it sorted by customer and then
by state, so what I would like to do is count the amount of orders per state
by each customer. For instance I have a customer that has placed 10 orders
for CA, 10 orders for NV, and ten orders for AZ. The excel spreadsheet lists
this customers name 30 times 10 for each state. But I have a fairly large
spreadsheet and don't wish to count how many orders this customer placed for
each state. Is there a formula to automate this with. I am sorry that was
pretty long winded. Please advise

2 categories
 

Ron, I was able to do it. Thank you very much. I really appreciate it.

"Ron Coderre" wrote:

I think a pivot table would satisfy your request.

Data.Pivot Table
Select Excel List
Select your data list
Layout:
€¢Set the rows to be Customer and State
€¢Set the data to be COUNT OF STATE
Select a cell to locate the Pivot Table on
Finish

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Joe Resler" wrote:

I am trying breakdown a spreadsheet of orders for business for the month of
october. I pulled data from my proprietary system and created an excel
spreadsheet. I have only two columns one is the customer name, and the other
is the state that the order originated. My customers place orders for
different states so for each order the customers name is listed and the state
they placed the order for is listed. I have it sorted by customer and then
by state, so what I would like to do is count the amount of orders per state
by each customer. For instance I have a customer that has placed 10 orders
for CA, 10 orders for NV, and ten orders for AZ. The excel spreadsheet lists
this customers name 30 times 10 for each state. But I have a fairly large
spreadsheet and don't wish to count how many orders this customer placed for
each state. Is there a formula to automate this with. I am sorry that was
pretty long winded. Please advise

2 categories
 

Did you put the State field in the two locations?
It should be included in the list of Rows AND as a data field (Count of
State).

So the Rows list should include Customer AND State.
That way you'll get totals by Customer by State.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Joe Resler" wrote:

I am having a little difficulty using the pivot table. I was able to do a
count of all orders by customer. But I have not found where to do a count by
state. Any ideas?

"Ron Coderre" wrote:

I think a pivot table would satisfy your request.

Data.Pivot Table
Select Excel List
Select your data list
Layout:
€¢Set the rows to be Customer and State
€¢Set the data to be COUNT OF STATE
Select a cell to locate the Pivot Table on
Finish

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"Joe Resler" wrote:

I am trying breakdown a spreadsheet of orders for business for the month of
october. I pulled data from my proprietary system and created an excel
spreadsheet. I have only two columns one is the customer name, and the other
is the state that the order originated. My customers place orders for
different states so for each order the customers name is listed and the state
they placed the order for is listed. I have it sorted by customer and then
by state, so what I would like to do is count the amount of orders per state
by each customer. For instance I have a customer that has placed 10 orders
for CA, 10 orders for NV, and ten orders for AZ. The excel spreadsheet lists
this customers name 30 times 10 for each state. But I have a fairly large
spreadsheet and don't wish to count how many orders this customer placed for
each state. Is there a formula to automate this with. I am sorry that was
pretty long winded. Please advise