Counting the Results of a Formula Using Find and Replace

Written by Allen Wyatt (last updated June 12, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021


2

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:

  1. Press Ctrl+F. Excel displays the Find tab of the Find and Replace dialog box.
  2. In the Find What box, type "Investigate" (without the quote marks).
  3. Using the Look In drop-down list, choose the Values option. (If you cannot see the Look In drop-down list, click the Options button.)
  4. Click Find All. Excel expands the Find and Replace dialog box downward to include a list of all cells found that contain the word "Investigate." At the bottom-left of the dialog box you can see a count of the number of matches found.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the Copy or Move Text Keys

Most people use the Clipboard to copy and move text in Word. Before the Clipboard, Word used F2 to move text and Shift+F2 ...

Discover More

Making Sure a Document Always Has an Even Number of Pages

For some documents, you may want to make sure that a printout always has an even number of pages. Word has no intrinsic ...

Discover More

Understanding the POWER Function

The POWER function is used to raise one value to another value. However, Excel also provides an exponentiation operator ...

Discover More

Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!

More ExcelTips (ribbon)

Finding and Replacing in Text Boxes

Finding and replacing information in a worksheet is easy. Finding and replacing in other objects (such as text boxes or ...

Discover More

Finding Based on Displayed Results

Want to use Excel's Find feature to locate cells based on what those cells display? It's easy if you know how to adjust ...

Discover More

Allowing for Prefixes and Suffixes in Find and Replace

Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 + 1?

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.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.