Reference a Page to calculate weight(Formula)
Hello Community,
I am trying to reference a page which has certain weights assigned to the categories which the users will select while filling out the form. Based on the selection of the categories the weight is calculated and the total weight is shown. I am attaching the Screenshot of the page which has the categories and their corresponding weights and the other sheet which has the formula to calculate the total weight.
Formula to Calculate Weight :
=SUM(VLOOKUP([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row))
Error :
Incorrect Argument Set
Screenshot Categories 1 shows the categories selected by the users while filling out the form and Screenshot Weight shows the corresponding Weight Value for each category.
Answers

Your vlookup isn't complete. It needs a range of columns to look at, a value to look for, a column to get a value from, and true or false. It still won't do what you are wanting though as it will pull only one value to your sum formula.
Could you give more context as to what you are trying to do?

Hi kyle
I am attaching 2 files out of which one file has the categories with their corresponding weights(VLookupRAC InitiativeEngagement Strategy) and the 2nd file has the formula for weights(Project Stakeholder Management Tool)
Based on the selection for each category like Stakeholder level of interest,Stakeholder Influence on Project,Level of Impact on the Stakeholder and Commitment of Stakeholder the Weight(C) is calculated. When the user makes this category selection a corresponding weight is calculated and the formula for the weight of each categories is as follows:
Interest(W)(Based on Stakeholder level of interest) : =VLOOKUP([Stakeholder level of interest]@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 2, false)
Influence(W)(Based on Stakeholder Influence on Project) : =VLOOKUP([Stakeholder Influence on Project]@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 2, false)
Impact(W)(Based on Level of Impact on the Stakeholder) : =VLOOKUP([Level of Impact on the Stakeholder]@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 2, false)
Commitment(W)(Based on Commitment of Stakeholder) : =VLOOKUP([Commitment of Stakeholder]@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 2, false)
P.S : These formulas need modification as they are throwing errors but just to give you an idea how the sheet is working.
Based on the Weight, I also want to calculate the Engagement Strategy for this Stakeholder.
Current Formula:
Weight(C) : =SUM(VLOOKUP([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row))
Engagement Strategy for this Stakeholder : =IF([Weight(C)]@row >= 15, "Collaborate (Key Players)  Requirement: strong buyin", IF([Weight(C)]@row >= 11, "Active Consultation to maintain confidence", IF([Weight(C)]@row >= 9, "Maintain Interest", IF([Weight(C)]@row >= 6, "Keep Informed  Monitor and Respond", IF([Weight(C)]@row >= 3, "Value and Respect Peoples and Cultures")))))
Please feel free to drop me a message if you have further questions.
Regards.

@skdimitri I think it would help you to have a few helper columns for each category that puts the weight there.
=Vlookup([Commitment of Stakeholder]@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 2, false) for each one and then just total the weights on the sheet itself with =Sum(Weight1@row:WeightX@row)

@Eric Law Can you elaborate this as the formula for the Vlookup is not working for me and where can I add the =Sum(Weight1@row:WeightX@row) in the sheet.
For your reference I have added the actual sheets with all the columns in it so can you please show how can I do it that would be very helpful.
Regards,
Shubham Kolekar.

If your sheets are like the files you have here.
For Stakeholder Weight, your formula should be =vlookup([Stakeholder level of interest)@row, {VLookupRAC InitiativeEngagement Strategy Range 1}, 3, false)
Do that for all 4 sections with the proper lookup
Create a TOTAL WEIGHT column and sum the 4 weights. Should just be =SUM([Stakeholder level of interest]@row:[Commitment of Stakeholder]@row)
Help Article Resources
Categories
Check out the Formula Handbook template!