CODEWITHSUNDEEP
vbaexcelexcel-formula
ylluminate
ylluminate

Reputation: 12369

How to categorize rows and count uniques in Excel?

I have a table of data that has a state and city column. I have sorted the table based on the two columns appropriately, however I am actually looking to just count the number of rows per each state and then another by cities for statistical analysis.

How do I count the unique entries in those columns and then display them in another portion of the table or in another table in the spreadsheet document? I am assuming that doing this programmatically may be best.

Upvotes: 0

Views: 1732

Answers (2)

ylluminate
ylluminate

Reputation: 12369

I have to say that a PivotTable does the trick. After about 3 minutes of playing around it became rather clear, although I have to say that the MS Office 2011 release I'm on is not very intuitive with regards to where to actually go.

Notwithstanding, I really liked @enderland's recommendation. True it was not the solution exactly, but I usually prefer programatic approaches for solutions. Thanks for that!

Upvotes: 0

enderland
enderland

Reputation: 14145

How do I count the unique entries in those columns and then display them in another portion of the table or in another table in the spreadsheet document? I am assuming that doing this programmatically may be best.

Something like this will be perfect

=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))

This formula will give the result you are looking for. You may wish to change it to

=SUM(IF(FREQUENCY(A:A,A:A)>0,1))

And it will summarize the entire A column without requiring a range.

I've undeleted this answer after your request, however, I am unsure it will do exactly what you are looking for as the first 1/2 of your question seemingly conflicts with the second half.

Upvotes: 1

Related Questions