Written by Allen Wyatt (last updated June 12, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
Hank has a worksheet for each item his company manufactures, listing in columns, among other data, the estimated and actual costs for each component used in manufacturing the item. There could be anywhere from 600 to 3,000 rows for each worksheet. In column AL there is a calculated Total Variance for each row. In column AW Hank displays the word "Investigate" if the Total Variance is out of bounds. He needs to count the number of times the word "Investigate" appears in this column, but has not been successful using Find and Replace to do this.
If you are bound and determined to use Find and Replace to get your count, then you should follow these steps:
That being said, using Find and Replace may not be the best approach. You may get extraneous information in your results, such as an instance of the word "Investigate" that appears in a different cell than one in column AW. Instead, consider just using a simple formula:
=COUNTIF(AW:AW,"Investigate")
This formula, if placed in a column other than AW, will return the total count of cells displaying "Investigate" in column AW. If there may be other characters on either side of the word (such as spaces), then you should change the formula slightly to include encasing asterisks:
=COUNTIF(AW:AW,"*Investigate*")
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13871) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Wildcard characters can be used within the Find and Replace tool, but what if you want to actually search for those ...
Discover MoreIf you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...
Discover MoreWhen doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-06-18 14:53:02
Philip
Another way to perform this type of counting is by using a pivot table …
2021-06-14 07:06:22
Mike D.
@Allen
What I love about your site is no matter how much I know (or think I know) I always learn more. I contributed to this answer and it is heart warming to know I did so in a positive manner.
What I learned is that you can reference a column entirely with just the letter [AW:AW] no matter how many rows are used with this formula. I wonder how many other places this will work.
Thank you for expanding my mind and teaching this old dog new tricks.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments