Formula

[stephens176]

I have a spreadsheet that has City in column A, and customer # in column B,
and a invoice number in column C, It is sorted by column A, then column B.
I need to count the number of invoices in column C for each customer in
column B and for each City in column A.

I tried the data subtotal function to count C at each change in column B,
which Counts number of customer invoices, but I also need to know for each
city (col A) how many invoices each customer had.

Sorry to be so wordy. Any Suggestions

 

[JE McGimpsey]

Use a Pivot Table:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

 

[mikearelli]

Create a pivot table based on the table you already have.
Put City and Cust# in left column. Put Count of Invoice Num in data section.

City CustNo InvNo
Boston 12345 1
Boston 12345 2
Boston 12345 3
Boston 12345 4
Boston 12345 5
Boston 22222 6
Boston 22222 7
Boston 22222 8
Boston 22222 9
Denver 33333 10
Denver 33333 11
Denver 33333 12
Denver 33333 13
Denver 33333 14
Denver 33333 15



Count of InvNo
City CustNo Total
Boston 12345 5
22222 4
Boston Total 9

Denver 33333 6
Denver Total 6

Grand Total 15