COUNTIF referencing another sheet and based on weight - can't get it to work

jmoran

Hello,

I am working on a capacity worksheet for my team. Essentially I am taking information from 8 different backlogs to calculate how much work has been assigned to them and comparing that against their capacity so we are able to see if they are under/over/correctly allocated.

What I am trying to do is from my Capacity worksheet is reference a backlog worksheet to COUNTIF a specific Analyst is listed as the Primary and then add their hours depending on what has been listed in the Implementation Effort column.

This formula works on the individual backlogs as a sheet summary field but when I took the formula to my other sheet to add a sheet reference and try to pull all my data together I am getting #UNPARSEABLE. I have used all my little AI friends and they all seem to think that this should work but I can't seem to get it.

Here is the formula (only change I made was to remove my Analyst's name):

=10 * COUNTIFS({HR Service Delivery} Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "5")
+ 20 * COUNTIFS({HR Service Delivery} Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "3")
+ 30 * COUNTIFS({HR Service Delivery} Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "2")
+ 40 * COUNTIFS({HR Service Delivery} Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "1")

Here are the two sheets I am working with so you can see the column and sheet names.

I am on the newer side to Smartsheet so any help would be much appreciated.

Thanks!

Jennifer

KPH

Hi Jennifer

Looks like you're doing great and I think you're almost there. Without looking in too much detail, it looks like the problem is just with the way you are using the cross sheet reference. See this part:

{HR Service Delivery} Status:Status

That is a cross sheet reference to a range you named "HR Service Delivery" followed by a column reference within the sheet to the column "Status". You cannot do that. It should be one or the other.

COUNTIFS({HR Service Delivery}, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "5")

or

COUNTIFS(Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "5")

Are you perhaps referencing an entire sheet when you set up the cross sheet reference and then trying to specify a particular column? If so, change the cross sheet reference - just select the column with the statuses in.

I hope that makes sense.

KPH

Hi Jennifer

Looks like you're doing great and I think you're almost there. Without looking in too much detail, it looks like the problem is just with the way you are using the cross sheet reference. See this part:

{HR Service Delivery} Status:Status

That is a cross sheet reference to a range you named "HR Service Delivery" followed by a column reference within the sheet to the column "Status". You cannot do that. It should be one or the other.

COUNTIFS({HR Service Delivery}, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "5")

or

COUNTIFS(Status:Status, "In Progress", [Primary Analyst]:[Primary Analyst], "ANALYST NAME", [Implementation Effort]:[Implementation Effort], "5")

Are you perhaps referencing an entire sheet when you set up the cross sheet reference and then trying to specify a particular column? If so, change the cross sheet reference - just select the column with the statuses in.

I hope that makes sense.

jmoran

Thank you so much!!

This comment was gold!! "Are you perhaps referencing an entire sheet when you set up the cross-sheet reference and then trying to specify a particular column? If so, change the cross sheet reference - just select the column with the statuses in."

I did need to adjust my references to each column of the sheet in the formula instead of trying to reference the whole sheet. So the formula ended up looking like the below and is now working great. Thank you so much!!

=10 * COUNTIFS({HR Service Delivery Status}, "In Progress", {HR Service Delivery Primary Analyst}, "ANALYST NAME", {HR Service Delivery Implementation Effort}, "5") + 20 * COUNTIFS({HR Service Delivery Status}, "In Progress", {HR Service Delivery Primary Analyst}, "ANALYST NAME", {HR Service Delivery Implementation Effort}, "3") + 30 * COUNTIFS({HR Service Delivery Status}, "In Progress", {HR Service Delivery Primary Analyst}, "ANALYST NAME", {HR Service Delivery Implementation Effort}, "2") + 40 * COUNTIFS({HR Service Delivery Status}, "In Progress", {HR Service Delivery Primary Analyst}, "ANALYST NAME", {HR Service Delivery Implementation Effort}, "1")

KPH

I knew you were almost there! 👏