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: