CODEWITHSUNDEEP
excelexcel-formulavba
Yan
Yan

Reputation: 3616

How to find a count of certain value in a whole workbook with multiple sheets in excel?

I have a workbook with multiple sheets of data.

In the first sheet I have a list of values, and I would like to find how many times they appear in the rest of the book. The count would appear in the adjacent cell.

Thank you very much in advance!

Upvotes: 0

Views: 7768

Answers (1)

brettdj
brettdj

Reputation: 55672

You can do it with an extra step and some tinkering with your formula

  1. Define a range name, AllSheet as =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
    (see pic below)

  2. To count all occurrences of "Fred" from A1:B10 of all sheets enter in =SUM(COUNTIF(INDIRECT("'" & AllSheets&"'!A1:B10"),"fred"))
    as an array formula (press Ctrl - Shift - Enter together)

the "'" & AllSheets&"'! portion ensures that sheets with spaces etc that produce names like 'My Sheet' will work

enter image description here

Upvotes: 2

Related Questions