Find a value based on criteria

Trying to calculate a score based on criteria that the user inputs.  On sheet 1 user enter Male or Female, their age, and number of PU they did and in Column D I need to calculate the score based on the data from sheet 1.  An example of a Male, 38, and 51 PU's would give a score of 9.4.  It would look under the Male chart, then the age bracket, then find the corresponding number of PU's they did in Column A and give the score.  Attached is an example.
count.xlsx
macone1976Incident ResponderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Insert this formula below your input cells on Sheet 2:

=OFFSET(Sheet1!$B$3,MATCH(B3,Sheet1!$A$4:$A$104,0),IF(B1="M",0,5)+IF(B2>=70,4,INT(B2/10)-2))

Regards,
-Glenn
macone1976Incident ResponderAuthor Commented:
Glenn

I tried to paste the formula but it is giving me a result of 0
Steven CarnahanNetwork ManagerCommented:
There seems to be a small flaw in the formula.  I put Female, 16 and 28.  It returns 9.5  when it should be 8.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

macone1976Incident ResponderAuthor Commented:
Cant seem to get it to work.
Steven CarnahanNetwork ManagerCommented:
Here is picture using Glenn's formula in cell B5 on Sheet 2 with your default input.

Formula
macone1976Incident ResponderAuthor Commented:
Aww i see, I need it the other way      Gender  Age PU, going across, not up and down like the image.
macone1976Incident ResponderAuthor Commented:
How do I adjust to make it work the way I need it to.
Glenn RayExcel VBA DeveloperCommented:
I made it work in an example workbook.  Please take a look.

-Glenn
EE-Count.xlsx
Glenn RayExcel VBA DeveloperCommented:
Well, your original example had the metrics in a single column.  See the attached example workbook for both possible solutions (i.e., in a column and in a row).

Regards,
-Glenn
EE-Count.xlsx
Steven CarnahanNetwork ManagerCommented:
Glenn,

This is a nice job however I still get 9.5 when I use F/16/28 but using the chart on Sheet 1 that shows it should be 8.  Using the criteria given in the example (M/38/51) it comes back correctly.  I was just doing some spot checking when I found the one from my example.
Glenn RayExcel VBA DeveloperCommented:
Yep, there's an error if the age is less than 20.  Here's the corrected formula for the original sheet layout:

=OFFSET(Sheet1!$B$3,MATCH(B3,Sheet1!$A$4:$A$104,0),IF(B1="M",0,5)+MATCH(B2,{0,30,40,50,60},1)-1)

and for the new layout:
=OFFSET(Sheet1!$B$3,MATCH(D2,Sheet1!$A$4:$A$104,0),IF(B2="M",0,5)+MATCH(C2,{0,30,40,50,60},1)-1)

Updated workbook attached.

-Glenn
EE-Count.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
macone1976Incident ResponderAuthor Commented:
Glenn,  Thank you so much for your help.  That worked perfectly.  I have just one more new question and my project will be finished, similiar to this question but different format.
Glenn RayExcel VBA DeveloperCommented:
Glad I could help.  I'll keep an eye out for your followup question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.