Using Contact List as matching criteria for SumIF Formula

Dawn McCallister

Dear Smartsheet Community ... I am stumped and exhausted my research efforts.

We are not ready to implement resource management, users are not bought in but we do have to find a way to understand the teams balance. These individuals work on projects and have daily activities to tend to. The goal is to first understand the volume of project work currently open by Assignee. We have provided each project with a score value (1 through 3). This value is found on the project plan at the task level. It is populate when the task becomes active. I am now trying to count the number of open task and the sum of the score for open task by assignee. I have created a new spreadsheet to start capturing this detail.

I am looking for the correct formula that will execute a SUMIF.

Range for the range for the criteria is located on the project plan (another sheet) and is the Assignee Column, Criteria is a column on the sheet where I need the results of the formula and is also a contact list. Where these two values match, I want a sum of the Score from the reference sheet.

I have explored using the Find functionality but I am not getting anything to work. Any advice or assistance is greatly appreciated.

Thank you

Dawn

Genevieve P.

Hi @Dawn McCallister (and @ChelseaH!)

Your formula is correct. The reason you're getting 0 is because the numbers in your source sheet in the Score column are seen as text and not numbers, so they're unable to be added together.

I can tell this because the number 3 is appearing on the left side of the cell instead of the right. Is there a formula in your Score column that could be causing this?

You can wrap a VALUE function around that formula to turn it back into a number! Or if you're using something like an IF statement to return numbers, just make sure the numbers aren't in "quotes" otherwise that turns it into text.

Let me know if this makes sense! If you're still having trouble, it would be useful to see what formula you have in the Score column.

Cheers,

Genevieve

ChelseaH

Hi Dawn!

To be sure that I understand what you are trying to accomplish:

You want a formula that will look at a list of contacts, find a specific contact, and sum their score. Is that correct?

If so, the syntax will be:

=SUMIF(assigned column, "name of assigned", score column)

If you can share some screenshots (with confidential data removed or enter some dummy data), I can help with the specifics of the formula.

Thanks,

Chelsea

Dawn McCallister

You are correct in your understanding of what I am trying to accomplish.

This is the sheet where I need the totals populated.

Let's use Brian Bowen as our example. I want to sum if Line 4 project sheet column "Assignee" = Brian Bowen (Same Column Properties on the above) sheet.

Column to Sum is the score column

First attempt

Results in 0 but if on the Project plan if I use filters, I get a the value of 12

I then started to try to use the FIND functionality as my research indicated that might help but then I went to receiving broken formula messages.

ChelseaH

Dawn,

I'm scratching my head on this one. It looks like you're on the right track. Could it be your Assignee column is set up to allow multiple contacts while the Team Member column is a single select? Are your Score and Line 4 columns formatted for text/number?

ChelseaH

Dawn,

Try this:

=SUMIF({Line 4 - Project Schedule Range 2}:{Line 4 - Project Schedule Range 2}, FIND("Brian Bowen", @cell) > 0,{Line 4 - Project Schedule Range 1}:{Line 4 - Project Schedule Range 1})

I found the concept on another Smartsheet Community thread:

https://community.smartsheet.com/discussion/34431/create-sumifs-formula-with-a-contact-criteria-in-a-multiple-contacts-range

Dawn McCallister

The did not work, it came back as "#unparseable", I had run across that article as well. I was hoping that you found an error in the formula that I may have missed when I attempted to recreate it.

This one absolutely, has me stumped.

Dawn

ChelseaH

Dawn,

I'm going to keep playing with it, but perhaps a more seasoned expert than I can solve this riddle. I'm very interested to know how to make this one work.

Sorry that I wasn't able to help you!

Chelsea

Dawn McCallister

No worries Chelsea ... We will wait for the experts, thank you for jumping on this one and trying to assist.

Genevieve P.

Hi @Dawn McCallister (and @ChelseaH!)

Your formula is correct. The reason you're getting 0 is because the numbers in your source sheet in the Score column are seen as text and not numbers, so they're unable to be added together.

I can tell this because the number 3 is appearing on the left side of the cell instead of the right. Is there a formula in your Score column that could be causing this?

You can wrap a VALUE function around that formula to turn it back into a number! Or if you're using something like an IF statement to return numbers, just make sure the numbers aren't in "quotes" otherwise that turns it into text.

Let me know if this makes sense! If you're still having trouble, it would be useful to see what formula you have in the Score column.

Cheers,

Genevieve

Dawn McCallister

Genevieve ... That was the issue! I had "quotes" in my formula on the the project sheet I was reference. Great Tip! I will be adding this to my trouble shooting tips! Thank you so much

Genevieve P.

I'm glad I could help! 🙂

Dawn McCallister

Genevieve .. Can I bother you with one additional formula question? When contact list are involved it tends to kick my butt.

Here is what I am trying to do, written as a statement and not a formula. If (contact name in column from referenct Sheet A = contact in column 1 of current sheet AND Reference Sheet A Status = "In Progress" then return number located in cell 4 of current sheet)

=IF(AND([Team Member]@row = {Line 4 - Project Schedule Range 2}, {Line 4 - Project Schedule Range 3} = "In Progress", [Line 4 Work Balance]@row, 0))

I am receiving an #incorrect argument set error

Genevieve P.

Hi @Dawn McCallister

No problem at all, I'd be happy to take a look.

So since you're doing a Cross-Sheet formula, I would recommend using a COUNTIFS formula to see if the COUNT of a row where that user is in the other sheet with "In Progress" is greater-than or equal-to 1.

Try something like this:

=IF(COUNTIFS({Line 4 - Project Schedule Range 2}, [Team Member]@row, {Line 4 - Project Schedule Range 3}, "In Progress") >=1, [Line 4 Work Balance]@row, 0)

Let me know if this makes sense!

Cheers,

Genevieve

Dawn McCallister

That does make sense and the formula works but it does not get me the results I am looking for. Let me take an additional step back and provide you with the managers requirement. I maybe going about this completely the wrong way to start with. The managers place a project into 3 levels and each level has an associated value. Level 1 = 1 Pt, Level 2 = 2 points and Level 3 = 3 points. They have deemed that a signal resource should have 5 - 7 points based on active task in the project. In other words if there is a task assigned to the resource then count the point value. The trick is the we only want to count the value once. Example Brian has 4 open task on the Line 4 project but we do not want to add those task, we just want to apply 3 points to his workload because he is working on that project currently. Brian may also have open task on other projects, and those would apply a point value and then I would SUM all values to determine the current state work load.

So I have built a sheet to capture the current value for each person on all activity projects, then I use the summary functionality on that sheet to summarize and determine who can take on more and who is over loaded based on the 5 - 7 scale. (the team is struggling to adopt the resource management functionality, they are not ready for that level)

Confused?

Dawn

ChelseaH

@Dawn McCallister ,

If I could jump in, it looks like you have a misplaced parenthesis in your formula to close the AND part of the formula and proceed with the IF formula. This is why you are getting INCORRECT ARGUMENT as a result.

Try this: (moved parenthesis from the end of the formula to be after "In Progress".)

=IF(AND([Team Member]@row = {Line 4 - Project Schedule Range 2}, {Line 4 - Project Schedule Range 3} = "In Progress"), [Line 4 Work Balance]@row, 0)

You also mentioned that you wanted the formula to return cell 4 on the current sheet if all of the criteria are met. The way you have it written now, the formula will return the Line 4 Work Balance data on the first cell that meets the criteria. Since I don't see that column in any of your screenshots, I'm not sure if that is a problem. If the column has 3 in every row, it should work like you want. If not, you'll want to change [Line 4 Work Balance]@row to the exact cell you want it to return. (example: [Line 4 Work Balance]4 )

If that doesn't work, can you share a screenshot that shows the Line 4 Work Balance column?

@Genevieve P. - good eye! It never occurred to me that the column was reading text and not numbers. WOW!

Dawn McCallister

@Genevieve P. - I may have responded to quick. I just added your formula to another project and it may actually be working better than I thought. Let me play this out in the portfolio and do some more vetting.