Count If referencing other sheets

potternm

Hi! I am looking to create a Count If function that references other sheets.

Using the example below, in the Opportunity Tracker sheet, I am looking to create the function in the Staff Allocated column. For each unique Client + Opportunity Name pairing, I want to determine the count of resources "Accepted by client". Each of the other sheets are named based on the Client column and they would need to reference the Opportunity Name and Engagement Status columns in those client sheets.

For reference, here's what the results should yield.

Thank you!

Paul Newcome

How many different client sheets do you have, and what is the end goal for pulling these counts together?

potternm

https://community.smartsheet.com/discussion/comment/428248#Comment_428248

Right now we only have 4 client sheets but this will continue to grow over time. I wasn't sure if the capability existed to reference only the specific sheet where the client Column = sheet name.

The end goal being that we can identify how many resources have been allocated to open projects to then identify any variances. For background, each client opportunity will have a total requested staff amount which we would use with the # of allocated staff to determine the number of open recs.

Paul Newcome

My suggestion would be to create a helper column in each of the sheets that has a basic column formula to output the client name on every row

="ABC"

Then you would create a row report that references each of the client sheets and is filtered to only pull rows of that specific status.

potternm

https://community.smartsheet.com/discussion/comment/428276#Comment_428276

Thank you- that makes sense! From there, is there a way to pull the count from the report into my Opportunity Tracker sheet?

Paul Newcome

The report would replace the opportunity tracker sheet.

My personal suggestion though would be a total restructure of your build where you have a master sheet that contains all clients and then use reports or Dynamic Views to share the filtered lists out with whoever needs to see them as needed. Then pulling your higher level metrics would be much easier to manage.