Excel Help: Build A Summary Table To Place Employees In Age Bands, By Mr. Excel
Strategy: This problem is more difficult.
- In column A, enter a variety of age ranges, such as “>=75”, “>=65”, “>=55”, “>=45”, etc. In column B, enter a formula =COUNTIF($D$ 2:$D$57,A61), as shown in Fig. 568.
However, the results of this formula are cumulative. The 17 employees
in the over-55 category include the one employee in the over-65 category.
The 41 employees in the over-35 category include all of the people in
the over-45, -55, and -65 categories.
To get the real answer for any age band, you need to subtract all of the previous age bands. Look at this table:
Row 62: need to subtract row 61
Row 63: need to subtract rows 61 & 62
Row 64: need to subtract rows 61:63
Row 65: need to subtract rows 61:64
The rule, then, is that you need to subtract from row $61 to the row above the current cell.
-
Edit the formula in row 62. As shown in Fig. 569, add –SUM(B$61: B61) to the formula. Adding this new part to the formula will subtract the sum of B$61:B61. It is important that you have a dollar sign only before the first 61.
-
Copy the formula down to the other rows.
The single dollar sign in just one portion of the reference allows the formula to be copied down. As you copy this formula down to the other rows, the portion subtracted will expand. As you look at Fig. 570, note that in row 65 the formula is subtracting Rows 61 through 64.
Additional Information: The solution above required two different formulas, one formula in 61 and a different formula in 62 through 65. Personally, I hate using two formulas. One workaround would have been to subtract from row $60:60. By having the anchor row be the row above the first row in the table, you could have used the same formula in all cells of the table.
Gotcha: At this point, the labels in column A are not technically correct. One solution would be to cut the formulas in column B and paste to column C, as shown in Fig. 571. It is important to use Cut and Paste instead of Copy and Paste so that the references keep pointing to column A. You can then type correct labels in column B and hide the information in column A by making the font white.
Summary: This particular use of COUNTIF is tricky. You almost need two conditions, which COUNTIF cannot handle. Luckily, the criteria were adjacent to each other, so you could subtract the results of the previous formulas to get the result for a particular age band.
Functions Discussed: =COUNTIF(); =SUM()
Images
