SUMIFS with External reference to a single select dropdown

Roberts1207

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.

Brett Wyrick

Hello there!

I am a little confused on what you're asking for, but just throwing this out there - would the COUNTIFS function work here instead of SUMIFS?

Roberts1207

https://community.smartsheet.com/discussion/comment/310343#Comment_310343

Hi Brett,

Thanks for your swift response, I can't get COUNTIFS or SUMIFS to work the way I want them to. I am struggling to try and describe my problem clearly, but maybe if I remove context it would be better.

I have two different Sheets.

Sheet A:

1. I have a column with values calculated by COUNTIFS.
2. The COUNTIFS reference another column within the sheet -the Project Type column. The cells of this column are a single select dropdown.
3. The COUNTIFS values recalculate based on the value selected in the Project Type dropdown, since the reference changes.

Sheet B:

1. I am trying to sum the calculated COUNTIFS values from Sheet A.
2. I am trying to do this by =SUMIFS ({COUNTIFS SHEET A}, {PROJECT TYPE SHEET A}, Project Type@row, etc.
3. Problem is, this only references the COUNTIFS value that is currently showing for whatever Project Type value is selected in Sheet A.
4. I want it to search the entire drop down in the cells of the criterion range of Sheet A for the criterion value in Sheet B and use the calculation for that specific value.

Paul Newcome

Are you able to provide some screenshots for reference?

Roberts1207

https://community.smartsheet.com/discussion/comment/310456#Comment_310456

Hi Paul,

Sure no problem! Please find below:

Sheet A:

So I have a sheet with one row per employee.

#of Projects per Type TOTAL is calculated using this formula utilizing an external reference:

When I change Project Type using the dropdown, this formula recalculates the #of projects:

Sheet B:

Using Sheet A as the source, I want to sum the number of projects for each resource by project type.

Currently the formula I am using is this:

In the Source sheet, Sheet A, if the Project Type isn't set to SBD in the dropdown, it returns zero for the above in Sheet B. But this isn't the case, Name 1 actually has 1 SBD project as seen in the first screenshot.

Is there a way I can modify my SUMIFS formula, to search the dropdown in Sheet A and take the respective number for that selection?

I'm thinking that I'll have to create multiple rows for each Name by each Project Type to achieve what I want to achieve, but just want to make sure there isn't a workaround considering there a lot of employees and a lot of project types.

Paul Newcome

Since you are referencing [Project Types]@row in your formula on Sheet A, it should update the number accordingly. Have you double checked to make sure spelling is an exact match between the two sheets including no extra spaces anywhere or anything?

Brett Wyrick

Does "SBD" have a space in it anywhere, on either sheet?

Roberts1207

https://community.smartsheet.com/discussion/comment/310708#Comment_310708

Hi Brett and Paul,

Thanks for your assistance. I was able to fix it just by separating out each project type for each person in the source sheet.

The SUMIFS worked correctly only when that Project Type was selected in the dropdown on the source sheet. I wanted to find a way where I didn't have to change the project type in the source in order for my SUMIFS to take the correct number. I wanted it to just search the dropdown selections and take the respective calculation.

I apologize, I was terrible at explaining my issue but I appreciate you taking your time to try and help me regardless.