#Excel - Count the number of occurrences of an element in a column

Question

I would like to know how do you count the number of occurrence of individual elements in a column.

Eg, I have a single column

Colors

Red
Red
Blue 
Red
Green
Orange
Blue
Green

I want the output in a separate column like,

Red - 3
Blue - 2
Green - 2
Orange - 1

I tried CountIf, didn't work that great for me. Tried using Pivot Table, I didn't know how to choose the rows to be K. I'm dealing with more than 100k rows, please help. :) Thank you!

Answer 1

I want to add something to Scott's answer.

After creating the pivot table with "colors" in the rows and values section, it is important to make sure the "Value Field Settings" are set to "count." It may not always default to that.

To change "Value Field Settings," you simply click where it says "Count of colors" or if it's not set to count, it may say "Sum of colors," or a few other things. A menu will appear and you click on "Value Field Settings." Select "Count" and click OK.

Answer 2

Pivot table is the way to go.

select all the rows with your data. Go to Insert --> Pivot Table.

Choose where you want to output the table

You want put the Colors in both Rows and Values.