Formula that crosses three sheets with multiple conditions

Daniel Arvesu

Hi there,

I am looking for help in creating a formula that will

• Sheet A: Look at Completion % column to determine which tasks are less than 100%
• Sheet A: Then, for each task with Completion % less than 100%, look at work hours remaining (WHR) column, and for each task owner sum these hours
• Sheet A & B: Then, take multiply each of these sums by an assigned, unique costing element located in a different sheet (Sheet B)
• Sheet A, B, & C: Then, sum those multiplied amounts in a cell of a third sheet (Sheet C)

I have been starting/failing with using VLookups, SUMIFs, etc., so I figure I'd go to the experts to see what they can do. Any help is most appreciated.

Thank you for your time.

L_123

=sumifs({Sheet A WHR},{Sheet A Task Owner},[Task Owner]@row,{Sheet A Completion},@cell <1)*index({Sheet B Costing Element},match([Task Owner]@row,{Sheet B Task Owner},0))

And then a simple sum should finish it off. You'll have to connect the references.

Daniel Arvesu

Thank you! Making sure I understand the elements in the above formula, correctly. When able, please confirm the following:

Sheet A WHR = Selecting the entire column in Sheet A?

Sheet A Task Owner = Selecting the entire column in Sheet A?

Task Owner = Typing out a string with the name of the Task Owner?

Sheet A Completion = Selecting the entire column in Sheet A?

Sheet B Costing Element = Selecting the Name and Costing element cells or just Costing element?

Sheet A Task Owner = Selecting the entire column in Sheet B?

L_123

Yes select full columns.

Yes select full columns.

task owner: you should have a column with the task owner you want to look up. If you don't have that, you can simply type in the correct reference into the formula in this position and it will still work yes.

Yes select full columns.

Just costing element, we already checked for name in the previous part of this formula

Yes select full columns

You need to make sure your spelling and references are correct/consistent to make this work. Let me know if you have any issues.

Daniel Arvesu

This is the formula that I am getting an "#UNPARSEABLE" error with:

=SUMIFS({PR-260-SD-HMH-Enterprise Solution Design Range 1}, {PR-260-SD-HMH-Enterprise Solution Design Range 2}, [Project Manager]3@row,{PR-260-SD-HMH-Enterprise Solution Design Range 3},@cell<1)*INDEX({*** DO NOT SHARE *** PMO - Costing Informa Range 2},MATCH([Project Manager]3@row,{*** DO NOT SHARE *** PMO - Costing Informa Range 1},0))

Where:

PR-260-SD-HMH-Enterprise Solution Design = Sheet A

*** DO NOT SHARE *** PMO - Costing Informa = Sheet B

Am I missing something?

L_123

[Project Manager]3@row

Should be

[Project Manager]@row

You have this in 2 locations.

Hopefully this solves it, but I stopped looking after finding this error. Let me know if it works, if not we can dig a bit deeper.

Daniel Arvesu

Fantastic! It worked! Thank you so much. Now, my next question is what if a "Sheet A" task has multiple "Task Owners" ??

L_123

Pop a contains in there.

=sumifs({Sheet A WHR},{Sheet A Task Owner},contains([Task Owner]@row,@cell),{Sheet A Completion},@cell <1)*index({Sheet B Costing Element},match([Task Owner]@row,{Sheet B Task Owner},0))

This should work for single and multi contact columns.

Edit:

Probably easier if I just modify your formula to have the contains. See below

=SUMIFS({PR-260-SD-HMH-Enterprise Solution Design Range 1}, {PR-260-SD-HMH-Enterprise Solution Design Range 2}, contains([Project Manager]@row,@cell),{PR-260-SD-HMH-Enterprise Solution Design Range 3},@cell<1)*INDEX({*** DO NOT SHARE *** PMO - Costing Informa Range 2},MATCH([Project Manager]@row,{*** DO NOT SHARE *** PMO - Costing Informa Range 1},0))

Daniel Arvesu

So now it is calculating out to $0.00 instead of the correct value. Any thoughts as to why?

L_123

Is the value in project manager the exact same as the value in PR-260-SD-HMH-Enterprise Solution Design Range 2? Are both formatted as contact columns?

Daniel Arvesu

Yes they are.

L_123

Try just the first part of the equation, what does this give you?

=SUMIFS({PR-260-SD-HMH-Enterprise Solution Design Range 1}, {PR-260-SD-HMH-Enterprise Solution Design Range 2}, contains([Project Manager]@row,@cell

Daniel Arvesu

Gives me $0.00 again.

Paul Newcome

Try swapping out the CONTAINS() for a FIND()>0. CONTAINS doesn't pick up on Contact Type columns, but FIND does.

Daniel Arvesu

Would the formula look like this?

=SUMIFS({PR-260-SD-HMH-Enterprise Solution Design Range 1}, {PR-260-SD-HMH-Enterprise Solution Design Range 2}, FIND()>0([Project Manager]@row, @cell), {PR-260-SD-HMH-Enterprise Solution Design Range 3}, @cell < 1) * INDEX({*** DO NOT SHARE *** PMO - Costing Informa Range 2}, MATCH([Project Manager]@row, {*** DO NOT SHARE *** PMO - Costing Informa Range 1}, 0))

Paul Newcome

It would look like this...

FIND([Project Manager]@row, @cell) > 0

Basically you replace the function itself and then tack on a "> 0" afterwards since the FIND function produces a numeric value.