How do I add age range

I am trying to take a list of all employees that includes their info like department, name, and age and then create a age range (like 16-24, 25-34, etc...) so I can then count how many employees fall under each age range.

I have tried two ways and neither work.


-The first and i think easiest is to have a helper sheet that contains my age range i want to use. That way on my main sheet i can use a vlookup formula to pull in the range result.

-here is my main sheet with the helper column called 'age group'. and the formula being used:

=VLOOKUP(Age@row, {HR DASH - HEADCOUNT (METRICS) Range 2}, 2, true

-and in that formula, here is the cross reference being used with my age range


The other idea I had, was to pull in counts on a helper sheet. But the formula keeps coming up with 0:

same main sheet as shown above

here is the helper/metrics sheet and the formula being used:

=COUNTIFS({Hr Age Range}, AND(@cell >= 16, @cell <= 24), {Hr Headcount by line of business}, [Line of Business]@row)

here are the cross references:


  • KPH
    KPH ✭✭✭✭✭✭


    Your first attempt looks good. You're missing a closing parenthesis but I think that is from pasting into here and not in your formula otherwise you'd see a different error. And your first row in the look-up should be 0-15 which is entirely unrelated and irrelevant but shows the level of scrutiny I have put your formula through. 😉

    I am wondering if the parenthesis in the cross-sheet reference is an issue. I have had problems before. It seems to be OK if you name a reference without parentheses and then rename it to include them, but if you go straight in with the parentheses in there it falls apart. I just try to avoid them.

    I tested it out, and it works for me.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!