How to Search an Entire Worksheet for a Value
Alex has a WORKSHEET that is wrought with several NAMES OR VALUES. He needs to know how many times the NAME of his friend, PETER, appears on the WORKSHEET. How will he do it?
To SEARCH ENTIRE WORKSHEET FOR A VALUE, the COUNTIF function can be used. It counts the number of times the VALUE appears and returns the VALUE as an integer.
Search entire worksheet for value
In the example below, the formula in Cell C5 is:
The syntax for this formula is:
The value in Cell C4 is searched for in the entire RANGE (SHEET 2) and the COUNTIF function displays the result numerically for all Cells that fulfills the CRITERIA.
- Open an EXCEL WORKSHEET
- Type the data into SHEET 1 and SHEET 2. On SHEET 1, we are searching for the number of times the name PETER appears. Open SHEET 2 at the bottom (CHECK FIGURE 1) of the WORKSHEET and type the data as shown
Figure 1- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
Figure 2- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
- Click on Cell C5 on SHEET 1
- Type COUNTIF( into the cell as shown in figure 3. To get the RANGE (the cells containing several values including the ones we are interested in), we need to select the cells on WORKSHEET 2. We could as well select all cells to make things easier. To do this, click on SHEET 2. You will see the content of the fx bar as shown in figure 4.
Figure 3- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
Figure 4- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
- Beside Column A, just above ROW 1 on the figure above, you will see an arrow
- Click on this arrow. This SELECTS the entire WORKSHEET and the fx bar displays what you have in figure 5
Figure 5- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
- Type a comma on the fx bar as shown in the figure below. Leave a space. Type C4 (WE ARE STILL INTERESTED IN PETER!)
Figure 6- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
- Now, click the ENTER button
Figure 7- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE
Alex has solved his problem in one-piece. The same approach works no matter the size of the data. Ensure you take the precaution of adhering to the above steps religiously to prevent working on this fruitlessly.