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: