Formula Assistance

I am attempting to automate my project list by scoring each project based on calculations in another sheet.

I would like to create a "Project Score" based on drop-downs in my project sheet. Sum=Complexity*weight + cost*weight + impact*weight +resources*weight + value*weight. The values of each category and weight are in another sheet. (pics attached for both sheets to reference).

Any assistance would be greatly appreciated.

Find more posts tagged with

Manufacturing

Formulas

Project Management

Comments

Paul Newcome

For this I will call the sheet where you are putting the formula "Target Sheet", and the sheet where your reference table is will be call "Reference Sheet".

This can be done using a series of VLOOKUPs within your SUM function.

We will also use the "Reference Another Sheet" option in the Formula Help Box (see below). Whenever you see {Reference Sheet Range 1}, that is where you will use this. To set this range, click on the "Reference Another Sheet" link when in the appropriate portion of the formula. You will then select both column of data in your Reference Sheet.

So give this a shot. All you should have to change from the below formula is where it says "Weight", simply enter the value you want each field weighted by...

=SUM(VLOOKUP(Complexity@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Cost@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Impact@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Resources@row, {Reference Sheet Range 1}, 2, false) * Weight, VLOOKUP(Value@row, {Reference Sheet Range 1}, 2, false) * Weight)

j.green

Paul-

Man, I can't thank you enough. Bonehead moment, all along I was not putting the SUM and going straight for the vlookup. 2 full days on this! Thank you for saving my sanity, everything worked perfect.

Paul Newcome

No worries. I've had more than a couple of bonehead moments this week.

Glad we could get it figured out for you though.

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…