Formula to organize and count the employee's age group breakdown

Akash

You can use COUNTIFS function to group your data. To count the employees in age groups 20-30, 30-40 and 40-50 respectively, I suggest you use these in each of your yellow cells:

=COUNTIFS(B2:B53,">=20",B2:B53,"<30")

=COUNTIFS(B2:B53,">=30",B2:B53,"<40")

=COUNTIFS(B2:B53,">=40",B2:B53,"<=50")

Note that I've used >= (greater than or equal to) for the lower age and < (less than) for the upper age in each formula, except for 50 where I used <= assuming no employees fall into the 50-60 category (for some reason). This is to avoid double counting (e.g. someone aged 30).

Remember too that I gave a more developed answer to your question last November: Calculating current age of employees through using Pivot Table

Please consider this formula. Enter it in F2 of your example and copy down.

=SUMPRODUCT(($B:$B>=VALUE(LEFT($E2,2)))*($B:$B<=VALUE(RIGHT($E2,2))))

The formula doesn't contain any of your criteria. These are in column E. Therefore, as you change the age limits in column E the formula supplies the corrected count. You also don't need to worry about the range because the formula takes the entire column, ignoring blanks.

Key to the solution is that in the age group 20-30, for example, the formula will count all that are both 20 or older (>=20) and 30 and younger (<=30). The way you have phrased your requirement the total of count will exceed the total of the counted. That is because those aged 30 and 40 will all be counted twice. For the 30 year olds, they will be counted in group 1 and group 2, the 40 year olds in groups 2 and 3.

Therefore you should change the groups to be 20-29, 30-39 and 40-59, avoiding everlaps. The same formula will work either way because it works with the numbers in column E. You can also fine tune the >= specs for each end of the group in the formula itself.

Hi

If you have Office 365, you can use the follwing FILTER function, in F2 & copy down. This will be OK for your data, but if you have a 40 in it then the formula will be incorrect for all the formulas that have been supplied.

You need to revise your age groups IE: =<30, =<40, =<50, =<60 etc or something along those lines. The formulas supplied can then be adapted.

=COUNT(FILTER(A:B,(B:B>=LEFT(E2,2)+0)*(B:B<=RIGHT(E2,2)+0)))

John_Ru

In your last formula, it needs to be "=<50"