Cell value to reference a tab

you can use an Indirect() to reference that cell and use the contents in another formula
HOWEVER
A sample sheet would help here, and possibly will enable a quicker and more accurate solution for you.


The forum does allow for spreadsheets to be uploaded direct to the forum -

Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Here are the instructions, found at the top of the page again

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for? Office 11 does not exist ...

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

You could try this:

=COUNTIFS(INDIRECT("'"&A1&"'!$B$4:$B$2941""),B12,INDIRECT("'"&A1&"'!$E$4:$E$2941"),$C$11,INDIRECT("'"&A1&"'!$O$4:$O$2941"),$A$10)

where A1 contains the text Sheet A.