CODEWITHSUNDEEP
excelvba
Java questioner
Java questioner

Reputation: 157

How to count same items on excel

I need to count same items in excel. In excel sheet in rows with following data. (large amount of data in one column).

data: natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity, natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity, natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity.

From this how i can get count of amenity , count of shop.

thank you

Upvotes: 1

Views: 8073

Answers (3)

brettdj
brettdj

Reputation: 55672

My Duplicate Master addin is another potential solution. While for your question as asked I would normally go with the PivotTable suggestion from Kash (but using dynamic ranges to capture any data size changes), the addin provides further flexibility and output options that may be of use

  1. From you question you want only a duplicate count, not values that only occur once (the addin can handle both dupes and uniques)
  2. Ignoring Case Sensitivity
  3. Ignoring any white spaces (spaces, line breaks, carriage returns, the non-printing Char 160)
  4. Ability to work across multiple sheets
  5. Highlighting, Deletion and Selection options as well as the summary option you need

enter image description hereenter image description here

Upvotes: 0

JDunkerley
JDunkerley

Reputation: 12495

While I would go with @Kash is answer.

If you know the row values you want, and asuming the data in column A, then you could use the formula:

=COUNTIF(A:A,"amenity")

replacing "amenity" with each value you want to count

Upvotes: 2

Kash
Kash

Reputation: 9019

Several ways to do it and listed out here: Count how often a value occurs

The Pivot table approach would be more organized and can be just refreshed if new entries are added. Insert a Pivot table and drag your "Data" field both in the Row Labels and Values of the pivot table (which defaults to Count of Values).

Pivot table created for your data

PS: Though you have tagged VBA for this question, please note this is not needed for this simple count.

Upvotes: 2

Related Questions