SUMIFS with External reference to a single select dropdown

Hi,

My apologies for my lack of brevity, I'm not even sure how to word or ask a question about this... I am obviously pretty new to Smartsheet.

Objective: To be able to flag projects where the assigned resources don't have a lot of experience with the specific project type.

Context:

I created a sheet (SHEET A) that's columns look like this: Resource Name, Resource Role, Project Type, # of projects by type.

I only have one row per resource, as project type is a single select drop down with all possible values, and #of projects by type auto recalculates based on project type selection.

#of projects by type is calculated utilizing COUNTIFS with external references to our multiple project portfolios contained within Smartsheet.

Anyways, I am trying to create the flag within our current portfolio of active projects. The relevant columns in this portfolio are as such: Project Type, Assignee to Role A, Assignee to Role B, Assignee to Role C, etc. I am trying to create SUMIFS that reference Sheet A which sums the #of projects by type with conditional logics for assignee and project type.

Now here's the issue: I can't figure out how to configure my SUMIFS in the portfolio sheet to search the entire drop down in the Project Type dropdown within Sheet A to find the matching project type, and use the relevant number based on this value/filter to include in the summation.

I have been trying to use CONTAINS criteria within SHEET A range, or HAS but I can't get it to work. I've also tried FIND, but I may be using this incorrectly.

Question: Am I doomed to have to separate out the project types in Sheet A so they each have their own count by resource per row? Or is there a reference logic I can use that accounts for the filtering logic of the Project Type cells? I was trying to be efficient by only having one row per Resource.

Sorry if this is confusing.