Excel Question

[BarkingGhostar]

I have a spreadsheet where the first column as a unknown number of colors that appear many times over. I can sort them, by color, and know easy enough the variety of colors but I need a count of how many times a color appears. Mind you this is a spreadsheet with +100,000 rows and maybe three dozen or so different colors.

Example:

brown
red
orange
yellow
yellow
white
red
green
purtple
brown
etc.

I need to know how many times each color is present without having to use the filter list three dozen times. Any quick way of figuring this out?

 

[JimKiler]

Is the color the value of each cell or the formatting and there is another value in each cell?

in other words does it look like

column 1
1 (red)
2 (brown)
3 (yellow)
4 (red)
etc

If it is the value Countif would be the answer. If it is not the value but the format it looks like this link will work https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html

 

[BarkingGhostar]

Sorry, I should have used something else, like apple, blueberry, cantaloupe, etc.

 

[mpo]

Pivot table will do this.

Select the column. Then select Pivot table.

When the dialog box comes up, drag the column heading to 'Rows', drag it again to 'Values'. The pivot table will give you a count of the number of times the individual color/fruit/etc type appears.

Row Labels Count of Color
brown 2
green 1
orange 1
purtple 1
red 2
white 1
yellow 2
Grand Total 10

 

[BarkingGhostar]

Thank you, sir, I just created my first test data set and used the Pivot tool function. I was lost on your description, but nifty--if not old--YouTube video demonstrated what you were suggesting and it worked. My test data set was only <100 rows and I want to apply this to something on the order of >100,000.

 

[Tirthankar Bose]

Thank you for the reply. I also have the same question.
And have got the solution with this thread

 

[BarkingGhostar]

Late last night I applied t to a data set with just over 170,000 rows with 33 unique entry types in said column and the Pivot table tool worked beautifully.

 

[JimKiler]

I love pivot tables. I need a shirt that says that. I can see the girls running toward me and screaming as i wear it, LOL.