Search entire worksheet for value

Search entire worksheet for value

Generic formula 

=COUNTIF(Sheet2!1:1048576,"apple")

Related formulas 

Search multiple worksheets for value

Count occurrences in entire workbook

Explanation

To search an entire worksheet for a value and return a count, you can use a formula based on the COUNTIF function.

In the example shown, the formula in C5 is:

=COUNTIF(Sheet2!1:1048576,C4)

How this formula works

The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203.

The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. 

Sheet2!1:1048576

Note: an easy way to enter this range is to use the Select All button.

For criteria, we use a reference to C4, which contains "John". COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to "John". 

Contains vs. Equals

If you want to count all cells that contain the value in C4, instead of all cells equal to C4, add the wildcards to the criteria like this:

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

Now COUNTIF will count cells with the substring "John" anywhere in the cell.

Performance

In general, it's not a good practice to specify a range that includes all worksheet cells. Doing so can cause major performance problems, since the range includes millions and millions of cells. When possible, restrict the range to a sensible area.

Search entire worksheet for value

Generic formula 

=COUNTIF(Sheet2!1:1048576,"apple")

Related formulas 

Search multiple worksheets for value

Count occurrences in entire workbook

Explanation

To search an entire worksheet for a value and return a count, you can use a formula based on the COUNTIF function.

In the example shown, the formula in C5 is:

=COUNTIF(Sheet2!1:1048576,C4)

How this formula works

The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203.

The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. 

Sheet2!1:1048576

Note: an easy way to enter this range is to use the Select All button.

For criteria, we use a reference to C4, which contains "John". COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to "John". 

Contains vs. Equals

If you want to count all cells that contain the value in C4, instead of all cells equal to C4, add the wildcards to the criteria like this:

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

Now COUNTIF will count cells with the substring "John" anywhere in the cell.

Performance

In general, it's not a good practice to specify a range that includes all worksheet cells. Doing so can cause major performance problems, since the range includes millions and millions of cells. When possible, restrict the range to a sensible area.