allocating to an age group using sumproduct

[mike ryan]

Hi All,

I'm sure I'm just making a basic syntax error here, but basically all I want to do is use sumproduct to return an age bracket value, where the persons age is in column C and the age bracket to be allocated to is in column l, with the "beginning" and "end" ages of each age bracket in columns J & K respectively.

For example,if a person is aged 18 the formula would be =SUMPRODUCT((J$2:J$12>=C2)*(K$2:K$12<=C2),L$2:L$12).

Unfortunately, this is returning a value of 0?

Any ideas?

I've attached a workbook for reference.

Thanks!!

[MarvinP]

Hi Mike,

See the attached that uses a Pivot Table that may be what you want. You can group by age and give average or counts of salaries.

No formulas are needed.

[ConneXionLost]

Give this a try:

Please Login or Register  to view this content.

Cheers,

[mike ryan]

Hi Guys,

I tried the VLOOKUP and it was just returning a #value error?

Marvin, that is EXACTLY what I needed the age brackets for, which sadly begs my next question, which is how do you do that?

I can do basic pivot tables based upon the data that is presented, but how do you get it to provide averages per age range etc...?

I appreciate that I'm probably asking a bit of a long winded question for this post, but if you could point me in the direction of any previous posts that you might have seen that could elaborate on how to do this, I'd really appreciate it.

Thank you both so much!

[MarvinP]

Hey Mike,

This is why I try to do a test file to show what I did. "One test = 1000 opinions".

You need to get a little closer to pivot tables and how to show totals, averages and stuff. Then you need to find "grouping" in pivot tables.

It is all there - you just need to click on the pivot table and find how to access that feature.

I've searched the net for "Excel 2003 Pivot Table Group By" and see what you find. Most grouping examples are by dates but you can group by numbers also, like I did in my sample.