December 4, 2020

How to search a value in different sheet of a Google Sheet file

Below is the content of Sheet28 in our example. The table contains several names.

Now in another sheet we need to find out the number of occurrences of a certain name in Sheet28.

The formula we have used in cell B2 of the current sheet is as follows.

=COUNTIF(Sheet28!1:1048576,A2)

We have used the COUNTIF function here which takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet28. Here you can manually select the range to search the value in the specific sheet(here Sheet28) as well.

Sheet28!1:1048576

For criteria, we use a reference to A2, which contains “Simon”. COUNTIF then returns 4, since there are 4 cells in Sheet28 equal to “Simon”. 

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

=COUNTIF(Sheet28!1:1048576,"*"&A2&"*")

Now COUNTIF will count cells with the substring “Simon” anywhere in the cells in sheet 28.

Note :
  • The COUNTIF function is not case sensitive. As shown in the example above we have provided the name in upper case even though it returned the proper count.

Leave a Reply

Your email address will not be published. Required fields are marked *