1

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

Improve this question
1
  • what did you try ? Do you have a code issue ? Commented Oct 26, 2011 at 14:35

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.