MS Excel: Counting instances of text strings in a column


I have a rather large spreadsheet approximately 200K rows.  One of the columns has text strings I need an individual count/tally of.  For example if the string 'dog' appears 100 times in those 200K rows, I need to know that number.  Is there an easy way to do this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try use countIF function for quick solution.
link2377Author Commented:
How can I get the CountIf function to work without having to manually insert each text string I want countrd in the range?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Example using countIF function:

>>How can I get the CountIf function to work without having to manually insert each text string I want countrd in the range?
You can referring to link above, hence try like:


For a better and more advanced handling, you can try use Sumproduct function.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Saqib Husain, SyedEngineerCommented:
You can use "Advanced filter" to generate a list of unique entries.

Against the unique entries you can use the COUNTIF function as explained above.
Create a Pivot Table and summarize data by Count.
link2377Author Commented:
In the attached file, how would I go about counting /tallying each instance of text in column 'E'?  2X Red, 1X Yellow etc.  For the size of file I have, I can't go in and enter each text string manually as a search key/value.
Saqib Husain, SyedEngineerCommented:
You forgot the attachment
link2377Author Commented:
Here is the example file.
Saqib Husain, SyedEngineerCommented:
Do you know how to create a pivot table as suggested by tilsant? If not then

Place your cursor anywhere on the data
Insert > Pivot table
You will see a pane on the right side of the screen
drag the code to row labels box
drag the  order to values box
select the dropdown in the values box
select Value field settings
Change it to Count

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Very well explained by ssaqibh. But, instead of using 'order' field for counting, it'll be safer to use 'code' field itself - in case if order code is missing in any of the rows (though i feel this would not happen).

Attached is a sample Pivot Table for your data.
Anytime your data gets added / deleted, simply go to PivotTable Tools > Options > Change Data Source.
link2377Author Commented:
Thank you both...First time I have ever created a pivot table.  Solution works great.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.