Calculating a total value based on weighted drown list

BOULMAN
BOULMAN used Ask the Experts™
on
Hello Experts
I am working in excel 2010.  I have a list that I am using multiple times in a drop down list.  I want a way to do a calculation based on the choice the user used.                             Item    Value
                                                                                                 Truck      2
                                                                                                 Car          1
The user can choose the truck or car in various cells of the sheet.  I then want a total value based on their text choice2.

Any suggestions?
Thanks
Jarrod
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Try SUMIFS.

If you are not able to fix this yourself, a sample workbook with desired output filled manually would help.
CorinTackNetwork Engineer

Commented:
I've create a sheet with a basic IF formula to accomplish what you want. The trouble with this is that if you have a lot of drop-down options, the formula can get too long (you're limited to a certain number of statements, though it's pretty long).

This has the same options you show, and then has a formula that uses these drop-down options to determine a value.
example.xlsx

Author

Commented:
I think that is on the right track but here are the two sheets I am using.  I my only have 1 or 2 more choices.

Thanks
PFTdaily-schedule-2015.xlsx
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Not sure exactly in which column you want the formula and you haven't added the desired output as well.
But if your drop-down list in column B, then try this.....

=VLOOKUP(B7,Sheet1!$A$2:$B$12,2,0)
and then copy down.

Is this what you are trying to achieve.

Author

Commented:
My hope was to have each drop down cell be linked to a value based upon what was picked.  Then either have a lab total or just a grand total from the three areas.  Not quite sure where to put the vlookup.
I filled out the sheet and manually entered the values.  That might help me explain what I need.
PFTdaily-schedule-2015.xlsx
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Format the column as General where you want to place the formulas.
I have placed the formulas in col. N and O. Not sure how you populated the third column for PFT Stress Values, by subtracting the values of Lab1 from Lab2 ?

Please refer to the attached.
PFTdaily-schedule-2015-1.xlsx

Author

Commented:
Very fast and extremely helpful.
THANKS!!!

Author

Commented:
Just had to tweak it a little.
Thanks
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome. Glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial