Calculate Totals Based on Age Restrictions & Years of Service

Hello all -

I am reaching out for some more expertise...

Per the attached, I am looking to add restrictions to my formula based on YEARS OF SERVICE per the age restrictions each person falls into, those with 0-4 YOS who are under 20 years old etc. etc.

Your help is much appreciated! THANKS.

Hi,

In your example, this would count how many are under 20 years of age and have 0-4 years of service...

Formula:

Please Login or Register  to view this content.

This would count how many are 20-29 years of age and have 5-9 years of service....

Formula:

Please Login or Register  to view this content.

Formula needs to be adjusted accordingly for each box.

I think this is what you mean, I have no idea if it's the most efficient way of doing it, but if I have understood you correctly then it does the job.

I have re-attached your example.

Is this any good for you?

Wow - you are awesome! Quite complicated, but yes it works!

Just out of curiosity, what does the two (--) after the "sumproduct" accomplish?

I tried applying your formulas with some modifications to another table involving gender. Do you mind having a quick look at the attached and seeing where I am making the mistakes? I am getting numbers that are quite high and which I know are wrong...

Jeff

You are welcome

The 2 -- make it return a count of the values that comply with the conditions as a pose to the sum of them.

The problem is to do with your use of equal to & greater/less than...


First problem.........

Your first column is entitled "<20", and your next column "20-29". This suggests that the first column is for people under 20 years, not including 20 years of age.

So you want to say <20 in your formula, not =<20

< means less than

=< means less than or equal to

You had =<20 in the first column, and =>20 in the second column, so both the first & second columns were counting people who are age 20.


Second problem...........

For the gender you were referring to =>"M", or =>"F", and so it probably considered the ones marked "M" to be greater than the ones marked "F", and so counted both for the females.

You just want to say ="M" & ="F" for this part.



I have reattached your example with these amendments made. Please let me know if this seems ok now.

Regards,