i8urkeyboard
New Member
- Joined
- Jul 5, 2022
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I work in a school and keep track of pupil progress. I have a countifs formula to track; who is 'working below target', 'working towards target', 'target met' and 'exceeded target' in percentages.
The formulas I have that calculate these are as follows:
=COUNTIFS(W3:W59,"<=0.0",W3:W59,"<=0.4")/COUNTIFS(B3:B59,"*") - ('working below target')
=COUNTIFS($W$3:$W$59,">=0.5",$W$3:$W$59,"<=1.9")/COUNTIF($B$3:$B$59,"*") - ('working towards target')
=COUNTIFS($W$3:$W$59,"=2.0",$W$3:$W$59,"<2.5")/COUNTIF($B$3:$B$59,"*") - ('target met')
=COUNTIF($W$3:$W$59,">=2.5")/COUNTIF($B$3:$B$59,"*") - ('exceeded target')
However, these formuals do not work if I have to use the filters. It still calculates all the array of cells. I have seen some threads of using =SUMPRODUCT formula as a workaround/solution but I just can't get my head around.
I work in a school and keep track of pupil progress. I have a countifs formula to track; who is 'working below target', 'working towards target', 'target met' and 'exceeded target' in percentages.
The formulas I have that calculate these are as follows:
=COUNTIFS(W3:W59,"<=0.0",W3:W59,"<=0.4")/COUNTIFS(B3:B59,"*") - ('working below target')
=COUNTIFS($W$3:$W$59,">=0.5",$W$3:$W$59,"<=1.9")/COUNTIF($B$3:$B$59,"*") - ('working towards target')
=COUNTIFS($W$3:$W$59,"=2.0",$W$3:$W$59,"<2.5")/COUNTIF($B$3:$B$59,"*") - ('target met')
=COUNTIF($W$3:$W$59,">=2.5")/COUNTIF($B$3:$B$59,"*") - ('exceeded target')
However, these formuals do not work if I have to use the filters. It still calculates all the array of cells. I have seen some threads of using =SUMPRODUCT formula as a workaround/solution but I just can't get my head around.
ELS Create Primary and Secondary.xlsx | ||||
---|---|---|---|---|
A | B | |||
63 | 15.8% | |||
64 | ||||
65 | ||||
Maths |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A63 | A63 | =COUNTIFS(W3:W59,"<=0.0",W3:W59,"<=0.4")/COUNTIFS(B3:B59,"*") |