The following data is a list of various pieces of data related to different grades in a K-12 school. You will see in the first 13 rows that there is a row for each grade.
In rows 21-25, I'm trying to build a series of sums based on the data from the top of the sheet. In these rows, you will see a lower and upper limit in the first 2 columns. I'd like to sum the data in the corresponding cells above based on those limits. Let me explain it from the perspective of F22. You will see that currently the formula is =sum(f4:f5) based on the grades 1 and 2. What I want to do is be able to change the upper or lower limit to dynamically see the sum of of more or less grades from the above data. I've tried several different functions but can't seem to find the right combination to accomplish this. Thanks in advance.
In rows 21-25, I'm trying to build a series of sums based on the data from the top of the sheet. In these rows, you will see a lower and upper limit in the first 2 columns. I'd like to sum the data in the corresponding cells above based on those limits. Let me explain it from the perspective of F22. You will see that currently the formula is =sum(f4:f5) based on the grades 1 and 2. What I want to do is be able to change the upper or lower limit to dynamically see the sum of of more or less grades from the above data. I've tried several different functions but can't seem to find the right combination to accomplish this. Thanks in advance.
| Current Fees.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | F | G | H | I | J | K | L | M | N | O | ||||||
| 3 | 0 | 2 | 0 | 8 | 10 | 0 | 0 | 0 | 0 | ||||||||
| 4 | 1 | 2 | 1 | 5 | 8 | 0 | 10 | 0 | 10 | ||||||||
| 5 | 2 | 2 | 1 | 3 | 6 | 0 | 8 | 0 | 8 | ||||||||
| 6 | 3 | 1 | 1 | 7 | 9 | 0 | 6 | 0 | 6 | ||||||||
| 7 | 4 | 0 | 0 | 6 | 6 | 0 | 9 | 0 | 9 | ||||||||
| 8 | 5 | 2 | 1 | 9 | 12 | 0 | 6 | 0 | 6 | ||||||||
| 9 | 6 | 5 | 1 | 3 | 9 | 0 | 12 | 0 | 12 | ||||||||
| 10 | 7 | 1 | 0 | 2 | 3 | 0 | 9 | 0 | 9 | ||||||||
| 11 | 8 | 1 | 0 | 3 | 4 | 0 | 3 | 0 | 3 | ||||||||
| 12 | 9 | 0 | 0 | 2 | 2 | 0 | 4 | 0 | 4 | ||||||||
| 13 | 10 | 2 | 0 | 2 | 4 | 0 | 2 | 0 | 2 | ||||||||
| 14 | 11 | 0 | 0 | 1 | 1 | 0 | 4 | 0 | 4 | ||||||||
| 15 | 12 | 0 | 0 | 1 | 1 | 0 | 1 | 0 | 1 | ||||||||
| 16 | |||||||||||||||||
| 17 | 18 | 5 | 52 | 75 | 0 | 74 | 0 | 74 | |||||||||
| 18 | |||||||||||||||||
| 19 | |||||||||||||||||
| 20 | |||||||||||||||||
| 21 | 0 | 0 | 2 | 0 | 8 | 10 | 10 | 0 | 0 | 0 | 0 | 10 | |||||
| 22 | 1 | 2 | 4 | 2 | 8 | 14 | 20 | 0 | 18 | 0 | 18 | 20 | |||||
| 23 | 3 | 4 | 1 | 1 | 13 | 15 | 20 | 0 | 15 | 0 | 15 | 20 | |||||
| 24 | 5 | 7 | 8 | 2 | 14 | 24 | 15 | 0 | 27 | 0 | 27 | 15 | |||||
| 25 | 8 | 12 | 3 | 0 | 9 | 12 | 9 | 0 | 14 | 0 | 14 | 9 | |||||
FCA Class Counts | |||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F17:H17,K17:M17 | F17 | =SUM(F3:F15) |
| I17,N21:N25,I21:I25,N17 | I17 | =SUM(F17:H17) |
| F21:H21,K21:M21 | F21 | =F3 |
| F22:H22,K22:M22 | F22 | =SUM(F4:F5) |
| F23:H23,K23:M23 | F23 | =SUM(F6:F7) |
| F24:H24,K24:M24 | F24 | =SUM(F8:F10) |
| F25:H25,K25:M25 | F25 | =SUM(F11:F15) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| H21:H25,M21:M25,U24:U26,H27,M27 | Expression | =J21-H21<0 | text | NO |
| H21:H25,M21:M25,U24:U26,H27,M27 | Expression | =J21-H21<1 | text | NO |
| H21:H25,M21:M25,U24:U26,H27,M27 | Expression | =J21-H21<2 | text | NO |
| I21:I25,N21:N25,V24:V26,I27,N27 | Expression | =J21-I21<0 | text | NO |
| I21:I25,N21:N25,V24:V26,I27,N27 | Expression | =J21-I21<1 | text | NO |
| I21:I25,N21:N25,V24:V26,I27,N27 | Expression | =J21-I21<2 | text | NO |
