SumProduct lookup

[bfis20]

Can someone look at the spreadsheet and tell me how to write or input a function that will return the value in cell F9 thru I12?

I wrote the formulas for F3:I6 and they work just fine.

[teylyn]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Also, there's no need to link to a 3MB image of the 50KB file you have attached.

[Paul]

Hi Bfis,

Not sure there's a worksheet formula you can use to get this done, but a macro or UDF would work well. Below I've posted two UDF's that you can add to a Standard Module in the VB editor then use worksheet formulas.

The first function (getNames) requires that you put the range that contains the city, the minimum age and the maximum age, e.g.

=getNames($E9,20,29)

The second function (getNames2) only requires you to put in the ranges that contain the city ($E9) and the cell with the age range (F$8). The age range must be formatted as "##-##", like 20-29 or 40-49.

=getNames2($E9,F$8)

The second formula you can fill down and right to fill your table. The first function would require you to change the min and max age for each column. Hopefully this makes sense and helps you out.

Please Login or Register  to view this content.

[DonkeyOte]

A UDF is certainly the most logical approach here but FWIW a roundabout way using formulae might be to

a) insert a couple of columns between Age & results matrix

b) add following formulae

Please Login or Register  to view this content.

c) add following formula to results matrix

Please Login or Register  to view this content.

the above is of course specific to your dataset example and so should your "real" file (if you have one) be different in terms of config. it goes without saying that the above is unlikely to work 100% and would require modification.


What I would say though is that if you were to sort your data A:C by A & C then things become far far simpler and a formula based approach could be "quite" efficient.