# 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**

**Formula**

In the example below, the formula in **Cell C5** is:

**=COUNTIF****(Sheet2!1:1048576,** **C4)**

The syntax for this formula is:

**=COUNTIF(range, criteria)**

**Explanation**

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**.

**Example **

**STEP 1**

- 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*

**STEP 2**

- 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 thebar as shown in figure 4.*fx*

*Figure 3- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE*

*Figure 4- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE*

**STEP 3**

**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*

**STEP 4**

- Type a comma on the
bar as shown in the figure below. Leave a space. Type*fx***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.

## Leave a Comment