Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get address of named range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get address of first cell in range in Excel
  • How to use Excel INDIRECT Function
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • Add Outline to Data in Excel
  • How To Create Pareto Chart in Excel
  • Managing Conditional Formatting Rules in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • How to create running total in an Excel Table

Data Validation Examples

  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation whole percentage only
  • Excel Data validation specific characters only

Count occurrences in entire Excel Workbook

by

This tutorial shows how to Count occurrences in entire Excel Workbook using the example below;

Formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&range),criteria))

Explanation

To count matches in entire workbook, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in D5 is:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))

where “sheets” is the named range B8:B10.

Context

This workbook has four worksheets. Three of the worksheets, “Sheet1”, “Sheet2”, and “Sheet3” contain 1000 random first names in a table that looks like this:

Worked Example:   Check if range contains a value not in another range in Excel

Note: the range we are using in the formula, A1:Z10000, is arbitrary and should be adjusted to suit your data.

How this formula works

Working from the inside out, we first have this expression inside the INDIRECT function:

"'"&sheets&"'!A1:Z10000"

Because “sheets” is a named range that contains “Sheet1”, “Sheet2”, and “Sheet3”, we get an array like this once the expression is evaluated:

{"'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000"}

Inside the array, we have three values, and each is a sheet name joined via concatenation to the range A1:Z10000. Notice that these are all text values.

Worked Example:   How to create dynamic reference table name in Excel

Next, the INDIRECT function is used to convert each text value to a proper reference, which are supplied to the COUNTIF function as the range argument, along with the value in D5 for criteria.

Worked Example:   Highlight duplicate columns in Excel

Since we’ve given COUNTIF three separate ranges, we get back three results in array like this:

{5;6;5}

Each item is a a count for one sheet.

Finally, SUMPRODUCT is used to sum the array, and returns a result of 16.

Post navigation

Previous Post:

Count rows with multiple OR criteria in Excel

Next Post:

Count numbers third digit equals 3 in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to count keywords in a range of cell
  • Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel
  • Split dimensions into three parts in Excel
  • How to get last line in cell in Excel
  • How to add a line break with a formula while joining text strings in Excel
  • How to calculate months between dates in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Convert Excel time to decimal hours in Excel
  • Add days to date in Excel
  • Convert date string to date time in Excel
  • Calculate interest for given period in Excel
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles