We can search **AN EXCEL WORKSHEET** for **a certain value** and get a **numeric count** for the number of occurrence of the **value** with the **COUNTIF** function. This article will walk through the process.

*Figure 1: How to Search an Entire Worksheet for a Value*

**Syntax**

**=COUNTIF(range, criteria)**

**Formula**

The formula in **Cell C5** is:

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

**Setting up the Data**

We will set up the data to search for **Peter** and **get a numeric count** by doing the following:

- We will open an
**EXCEL WORKSHEET**

- We will type the data into
**SHEET 1**and**SHEET 2.**We are searching for the number of times the name**PETER**appears in**SHEET 2**.

* Figure 2: Setting up the Data*

**Search Entire Worksheet for Value**

To search **SHEET2 **for the name** PETER, **we will do the following:

**We will click on Cell C5 in SHEET1**

- We will insert the formula below into
**Cell C5**

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

**We will press the enter key**

*Figure 3: Result of the Search and Count of PETER in the Entire Worksheet*

**Explanation**

The value (**PETER**) in **Cell C4** is searched for in the entire **RANGE (SHEET 2)** and the **COUNTIF** function displays the numeric result for all **Cells** that fulfills the **CRITERIA**.

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment