Count a value in multiple sheets in a workbook

Question

I have one workbook. The workbook has 12 sheets.

Each sheet is named something like Bob Smith,. Jim Jones etc.

Each sheet has one named table , Table1, Table2, etc

Each table has a Status column with status like Open, Closed, Pending

I have a Dashboard Sheet. I want a formula that would go through each table and count the number of Closed. I also want to count the number of Open and Pending. Each count in a different cell.

Example: Sheet1!table1 has 4 "Closed" Sheet2!Table2 has three "Closed" . If those are the only sheets that have a status of Closed, then the formula would provide the value of 7.

I’ve been messing with Indirect and CountIF but I can’t seem to figure it out.

Please edit your question to provide more background details on your problem. Telling readers about your research, what you have already tried, and why it didn’t meet your needs will allow them to understand it better. Please also edit your question to include a minimal reproducible example so that readers can run your code to answer your question. — Michal
– Michal, Commented May 1 at 20:04
Are you expecting the solution to work if new tables are added? Not possible unless you're using VBA unless they are always lined up in same rows/columsn? — pgSystemTester
– pgSystemTester, Commented May 1 at 20:05
If you name the tables all you would do is countifs( table1[status],"Closed") + countifs(table2[status],"Closed") Not a very exciting solution, but one available since tables are named ranges. You could also query all of the tables into a single query via Power Query and have it output a count by the status values or do the countifs against the aggregate query table. — Mark S.
– Mark S., Commented May 1 at 20:08

pgSystemTester · Accepted Answer · 2025-05-01 20:18:58Z

2

To get indirect to work you could do something like this: build another table with the appropriate references. So if you listed the other tables in a row, and then the other column names would be the status terms, you could get a sum and easily get a total.

Use this formula in the remaing columns besides table name:

=LET(iName,[@tableName],iTerm, B$3,zStatusCol,
MATCH("Status",INDIRECT(iName&"[#Headers]"),0),COUNTIF(INDEX(INDIRECT(iName),,zStatusCol),iTerm))

answered May 1 at 20:18

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count a value in multiple sheets in a workbook

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related