I need an excel formula

I need an excel formula to correctly select the position pay amount when that position number is selected from the drop down on the Projects Tab. The pay amount information is on Sheet1.

I am currently using the following formual to calculate how much based on certain criteria:

=COUNTIF(B12:O12,"1")*500+COUNTIF(B12:O12,"1LM")*500+COUNTIF(B12:O12,"1LM")*Sheet1!$G$1+COUNTIF(B12:O12,"L")*Sheet1!$G$2+SUM(A12*Sheet1!$G$3).

Where the 500 is listed is where i need it to be dynamic and based upon what is selected on the drop down.

Ideas and suggestions are welcomed. Please see the attached.
10-5-11.xlsx
LVL 1
wrt1meaAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
I assume both 500s are dynamic as per teylyn's version

a little simpler to SUM the COUNTIFS and multiply by the VLOOKUP then it doesn't need to repeat....also SUM at the end is redundant.....

=SUM(COUNTIF(B12:O12,{1,"1LM"}))*VLOOKUP($H$5,Sheet1!$E$1:$F$15,2,FALSE)+COUNTIF(B12:O12,"1LM")*Sheet1!$G$1+COUNTIF(B12:O12,"L")*Sheet1!$G$2+A12*Sheet1!$G$3

regards, barry
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello,

in your file the value for Position 5 is 104, so the result will be different from the formula with the 500 hard-wired.

=COUNTIF(B12:O12,"1")*VLOOKUP($H$5,Sheet1!$E$1:$F$15,2,FALSE)+COUNTIF(B12:O12,"1LM")*VLOOKUP($H$5,Sheet1!$E$1:$F$15,2,FALSE)+COUNTIF(B12:O12,"1LM")*Sheet1!$G$1+COUNTIF(B12:O12,"L")*Sheet1!$G$2+SUM(A12*Sheet1!$G$3)

cheers, teylyn
0
 
dlmilleCommented:
Try this:

=COUNTIF(B12:O12,"1")*VLOOKUP(H5,Sheet1!$E$1:$F$15,2,0)+COUNTIF(B12:O12,"1LM")*500+COUNTIF(B12:O12,"1LM")*Sheet1!$G$1+COUNTIF(B12:O12,"L")*Sheet1!$G$2+SUM(A12*Sheet1!$G$3)

The VLOOKUP is using the drop down value to seek a result on Sheet1 in E:F, and selecting the 2nd parameter (the amount) with 0-exact match.

Just extend E1:F15 down if more positions are required in future...

Dave
0
 
wrt1meaAuthor Commented:
Works great! Thanks for the support!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.