Count Unique values for by category

Question

I have a table with two columns, the first column has the neighbourhood name, and the second column contains a tree species. The table contains tens of thousands of rows (ie. there are multiple trees of each species in each neighbourhood) I need to know the number of different tree species in each neighbourhood. I got as far as using a pivot table to summarize and count the trees occurring in each neighbourhood, but the information that I am looking for is even more simple than this. How many unique tree species occur in each neighbourhood. I am using excel 2011 for mac. Can someone help me figure out the best way to do this?

Answer 1

With a PivotTable, one column for ROWS and the other for COLUMNS (either for Count of VALUES) you should only need to count the entries (either by row or by column, depending upon whether trees or neighbourhoods, and which you chose where).

Answer 2

If you don't want Pivot Tables, try the SubTotals feature as described by "rob" here: https://superuser.com/a/405569

You can also do this with the Subtotal feature.

• Click the Data tab in Excel's ribbon toolbar
• Click the Sort button and sort by your category column
• Click the Subtotal button and fill in the dialog as appropriate, then click OK For example...

At each change in: Category

Use function: Sum

Add subtotal to: Cost of Goods Sold

i.e. have column titles in row 1, sort by neighbourhood, and subtotal "for each change in neighbourhood, use the count function and add the subtotal to trees"