Return Unique Values from Table

[EU]

I have a table of redundant values in columns A and B
(Cost Center and Location, "Sheet1"), and I am trying to
get a return of the UNIQUE row combinations of Cost Center
and Location as well as the COUNT of them on Sheet2.

My goal is to save myself from having to manually Subtotal
the table, Go To--Special--Visible Cells Only, copy/paste,
etc. So, if I have a table of 20,000 entries of Cost
Center and Location(City) in Colums A & B, I want a return
of the unique combinations and the count of them. So, out
of a 20,000 row worksheet(Sheet1), Sheet2 will return in
cell: A2 "12345", cell B2 "Denver", and cell C2 "50".

My intuition led me to try a DCOUNTA formula, however it
did not work. I don't quite understand the syntax so I
don't know if I created it correctly--or know if it's even
appropriate in this instance.

If anyone knows how to do this, or if there's a better
solution than using a DCOUNTA formula I'd appreciate it.

Thanks.

EU

 

[Bernie Deitrick]

EU,

You can do this very easily with a Pivot table. Select your table and choose Data | Pivot Table....

Use both Cost Center (first) and Location (second) as row fields, then use Location as your data item, and Excel will give the count
of each location for each Cost Center: exactly what you want.

HTH,
Bernie
Excel MVP