How to search multiple worksheets for a value in a workbook and return a count of the value

A description of how to search multiple worksheets for a value in a workbook and return a count of the value

This blog post will show how to search multiple worksheets for a value in a workbook and return a count of the value.

Suppose you have four worksheets in your workbook

      ODI 

 
T20 
Test
 

Display the count of player by country, as shown below:

 
 

The output should look like the following after using the formulas: 

 

Solution (for the country count with all game categories, ODI, T20, andTest)

Create an Excel Workbook with three worksheets, as shown in the problem statement
 
Column A (Country) - contains Country names
 
Column B (Count of Player) – Count of players will be return in Column B
 
Column C (Sheets) – Column C contains Worksheet names which will be used to compare worksheet names with the country names in column A.
 
Open Player Count Worksheet, select Column B and paste following Formula in the B2 Cell:
 
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!C:C"),A2))
 
 

The formula will count the names whose Country is IND A2 and will search for text IND (column Cà!C:C ) in three Worksheets (Used in Formula àC2:C4) ODI, Test, and T20.

The above formula returns:

 
 
 
Repeat step 2 for Column B3 to B9

=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C4&"'!C:C"),A3))

Note - Just change the column number in the formula like A3/A4…../A9 with the respective column B3/B4…./B9

You will get the following output
 
 
For Country and Sport Category (ODI, T20, Test)
 
Create another table in Excel Workbook named “Count Payer” created earlier.
 
Column F (Country) - contains Country names
 
Column G (T20) – Count of players of T20 format with respect to Country will be return in Column B
 
Column H (ODI) – Count of players of ODI format with respect to Country will be return in Column B
 
Column I (Test) - Count of players of TEST format with respect to Country will be return in Column B
 
  
 
For the count of T20 players
 
Open Player Count Worksheet and select Column G and paste following Formula in the G2 Cell

=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G1&"'!C:C"),F2))

 
 

The formula will count names whose Country is IND F2 and it will search for text IND (column Cà!C:C ) in the Worksheet (Used in Formula àG1:G1)T20.

Above formula returns:

 
 
 
Repeat step 2 for Column G3 to G9
 
=SUMPRODUCT(COUNTIF(INDIRECT("'"&G1:G1&"'!C:C"),F3))
 
Note – Just change Column number in the formula like F3/F4…../F9 with respective column G3/G4…./G9
 
For the count of ODI players
 
Open Player Count Worksheet and select Column H and paste following Formula in the H2 Cell

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H1&"'!C:C"),F2))

 
 

The formula will count the names whose Country is IND F2 and it will search for text IND (column Cà!C:C ) in the Worksheet (Used in Formula àH1:H1)ODI.

The above formula returns:

 
 
 
Repeat step 2 for Column H2 to H9

=SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H1&"'!C:C"),F3))

Note – Just change Column number in formula like F2/F3…../F9 with respective column H2/H3…./H9.

 Output will look like
 
 
 
For the count of TEST players
 
Open Player Count Worksheet and select Column I and paste the following Formula in the I2 Cell

=SUMPRODUCT(COUNTIF(INDIRECT("'"&I1:I1&"'!C:C"),F2))

The formula will count names whose Country is IND F2 and will search for text IND (column Cà!C:C ) in the Worksheet (Used in Formula àI1:I1)ODI.

The above formula returns:

 
 
 
Repeat step 2 for Column I3 to I9

=SUMPRODUCT(COUNTIF(INDIRECT("'"&I1:I1&"'!C:C"),F3))

Note – Just change the Column number in the formula like F3/F4…../F9 with respective column I3/I4…./I9.

Output will look like:
 
 

Congratulations!! Your finished formula for Excel count and Whole output should look like the following: