Countif/s filtered

i8urkeyboard

New Member
Joined
Jul 5, 2022
Messages
6
Office Version
  1. 365
Platform
  1. 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.

ELS Create Primary and Secondary.xlsx
AB
6315.8%
64
65
Maths
Cell Formulas
RangeFormula
A63A63=COUNTIFS(W3:W59,"<=0.0",W3:W59,"<=0.4")/COUNTIFS(B3:B59,"*")
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
ELS Create Primary and Secondary.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1PupilEND OF 2020 -2021 LEVELSAutumn 2021Spring 2022Summer 2022ProgressEnd of Year Targets
2SiteNameYear GroupGenderKSColumn1Rising StarsPointsColumn2Rising Stars Points ProgressColumn5 Rising StartsRising Stars Points Progress Column13Rising Stars Points Progress Column15Current Points Progress Rising StarsCurrent Points Progress Vs. TargetCurrent Points Progress Vs. AspirationalColumn18End of Year TargetEnd of Year Target PointsAspirational Progress InputAspirational Progress
3ELPupil 113FKS 53 Sec304 Dev32.02.05 Sec36.04.06 Sec+39.53.59.52.52.04 Sec+33.55 Beg34.0
4ELPupil 211MKS 4Entry 1.030Entry 1.030.00.0Entry 1.431.01.0Level 1.0398.09.0-8.0-10.0Level 1.039.0Level 1.541.0
5ELPupil 37MKS 33 Beg283 Beg28.00.04 Sec33.05.05 Sec+36.53.58.53.02.53 Sec30.03 Sec+30.5
6CSPupil 48FKS 33 Sec30.0Entry 3.136.56.56.5
7ELPupil 511MKS 43 Sec304 Dev32.02.04 Sec33.01.05 Sec+36.53.56.5-0.5-1.04 Sec+33.55 Beg34.0
8ELPupil 67MKS 33 Sec304 Dev32.02.04 Sec33.01.05 Sec+36.53.56.5-0.5-1.04 Sec+33.55 Beg34.0
9ELPupil 710MKS 43 Sec30Entry 3.136.56.5Entry 3.136.50.06.5-2.5-4.5Level 1.039.0Level 1.541.0
10ELPupil 87MKS 3P8211 Dev23.02.02 Beg25.02.02 Sec+27.52.56.50.50.01 Sec+24.52 Beg25.0
11ELPupil 910MKS 42 Sec+27.53 Dev29.01.53 Sec30.01.04 Sec+33.53.56.0-0.5-1.03 Sec+30.54 Beg31.0
12ELPupil 108MKS 34 Sec+33.55 Dev35.01.56 Dev38.03.06 Sec+39.51.56.01.51.05 Sec+36.56 Beg37.0
13CPPupil 114MKS 201 Sec24.03 Dev295.05.0-1.0-2.02 Beg25.02 Dev26.0
14CSPupil 1210FKS 4Entry 2.534.0Entry 3.7384.04.0
15ELPupil 136MKS 2P8211 Dev23.02.01 Dev23.00.01 Sec+24.51.53.5-1.5-2.01 Sec+24.52 Beg25.0
29ELPupil 2711MKS 45 Sec+36.5Entry 1.331.0-5.5Entry 2.334.03.0Level 1.0395.02.50.00.0Entry 2.034.0Entry 2.534.0
30ELPupil 289MKS 32 Beg253 Beg28.03.02 Dev26.0-2.02 Sec+27.51.52.5-4.0-4.53 Sec30.03 Sec+30.5
31ELPupil 2911MKS 4Entry 3.136.5Entry 3.637.00.5Entry 3.738.01.0Level 1.0391.02.5-1.0-3.0Level 1.039.0Level 1.541.0
32ELPupil 305MKS 23 Beg283 Beg28.00.03 Sec30.02.03 Sec+30.50.52.50.0-0.53 Sec30.03 Sec+30.5
33ELPupil 319FKS 34 Sec+33.55 Dev35.01.55 Sec36.01.02.5-0.5-1.05 Sec+36.56 Beg37.0
34CPPupil 324MKS 22 Sec27.03 Beg28.01.03 Dev291.02.0-2.0-2.53 Sec30.03 Sec+30.5
35CPPupil 334MKS 24 Dev32.04 Sec33.01.05 Beg341.02.0-0.5-1.04 Sec+33.55 Beg34.0
36ELPupil 3411MKS 4Level 2.346Level 1.842.0-4.0Level 2.043.01.0Level 2.5485.02.0-5.0-9.0Level 2.548.0Level 2.952.0
37ELPupil 359MKS 34 Beg314 Dev32.01.04 Sec33.01.02.0-0.5-1.04 Sec+33.55 Beg34.0
38ELPupil 369FKS 36 Dev386 Dev38.00.07 Beg40.02.02.00.50.06 Sec+39.57 Beg40.0
39CSPupil 3713MKS 5Entry 2.835.5Entry 3.537.01.51.5
40ELPupil 388MKS 36 Dev386 Sec39.01.06 Sec39.00.06 Sec+39.50.51.5-1.0-2.07 Beg40.07 Dev41.0
41ELPupil 399MKS 31 Dev231 Dev23.00.01 Dev23.00.01 Sec+24.51.51.5-1.5-2.01 Sec+24.52 Beg25.0
42ELPupil 409MKS 32 Dev26.02 Sec+27.51.51.5
43ELPupil 4111FKS 44 Dev324 Dev32.00.04 Sec33.01.01.0-0.5-1.04 Sec+33.55 Beg34.0
44ELPupil 425MKS 22 Beg252 Beg25.00.02 Dev26.01.01.0-1.0-1.52 Sec27.02 Sec+27.5
45ELPupil 437MKS 36 Beg376 Beg37.00.06 Dev38.01.0Entry 3.8380.01.0-1.0-1.56 Sec39.06 Sec+39.5
46CSPupil 4410MKS 4Level 1.039.0Level 1.239.50.50.5
47ELPupil 456MKS 25 Sec365 Sec36.00.05 Sec+36.50.50.5-1.5-2.56 Dev38.06 Sec39.0
48ELPupil 4610MKS 44 Sec+33.5Entry 3.236.53.0Entry 2.234.0-2.50.5-5.0-7.0Level 1.039.0Level 1.541.0
49ELPupil 4710MKS 45 Sec+36.5Entry 3.437.00.5Entry 3.537.00.00.5-2.0-4.0Level 1.039.0Level 1.541.0
50ELPupil 4810FKS 41 Sec+24.51 Sec+24.50.02 Beg25.00.50.53.00.51 Beg22.01 Sec+24.5
51ELPupil 11410MKS 46 Beg377 Dev41.04.0Entry 3.537.0-4.00.0-2.0-3.0Level 1.039.0Level 1.440.0
52CSPupil 1218MKS 35 Beg34.04 Sec+33.5-0.5-0.5-2.5-3.05 Sec36.05 Sec+36.5
53ELPupil 12210FKS 45 Sec+36.5Entry 3.136.50.0Entry 2.936.0-0.5-0.5-3.0-5.0Level 1.039.0Level 1.541.0
54ELPupil 1239MKS 37 Sec42Level 1.139.5-2.5Entry 3.236.5-3.0Level 1.5414.5-1.0-4.5-6.5Level 1.541.0Level 2.043.0
55ELPupil 12410MKS 46 Sec+39.5Entry 3.637.0-2.5Entry 3.938.01.0-1.5-1.0-3.0Level 1.039.0Level 1.541.0
56CSPupil 12511MKS 4Entry 2.735.0Entry 1.833.0-2.0-2.0
57CSPupil 12611MKS 4Level 1.139.5Entry 3.437.0-2.5-2.5
58ELPupil 12710MKS 46 Sec39Entry 3.136.5-2.5Entry 3.336.50.0-2.5-2.5-4.5Level 1.039.0Level 1.541.0
59ELPupil 12811MKS 4Entry 2.334.0Entry 2.635.01.02 Sec+27.5-7.5-6.5-6.0-8.0Level 1.541.0Level 2.043.0
60
61Working Below TargetWorking towards TargetTarget MetExceeded Target
62
6315.8%21.1%8.8%54.4%
64
65
Maths
Cell Formulas
RangeFormula
A63A63=COUNTIFS(W3:W59,"<=0.0",W3:W59,"<=0.4")/COUNTIFS(B3:B59,"*")
C63C63=COUNTIFS($W$3:$W$59,">=0.5",$W$3:$W$59,"<=1.9")/COUNTIF($B$3:$B$59,"*")
F63F63=COUNTIFS($W$3:$W$59,"=2.0",$W$3:$W$59,"<2.5")/COUNTIF($B$3:$B$59,"*")
I63I63=COUNTIF($W$3:$W$59,">=2.5")/COUNTIF($B$3:$B$59,"*")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
X3:X59Cellcontains a blank value textNO
W3:W59Cell Value<0textNO
X3:Y59Cell Value<=-0.5textNO
X3:Y59Cell Value>=0.5textNO
X3:X59Cell Valuebetween 0.5 and 1textNO
X3:Y59Cell Value=0textNO
W3:W59Cell Value>=2.5textNO
W3:W59Cell Value=2textNO
W3:W59Cell Valuebetween 0.5 and 2.9textNO
W3:W59Cell Value=0textNO
L3:L15,Q3:Q15,L17,L20:L59,Q18:Q59,U4:U59Other TypeColor scaleNO
W3:Y59Cellcontains a blank value textNO
 
Upvote 0
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1PupilEND OF 2020 -2021 LEVELSAutumn 2021Spring 2022Summer 2022ProgressEnd of Year Targets
2SiteNameYear GroupGenderKSColumn1Rising StarsPointsColumn2Rising Stars Points ProgressColumn5 Rising StartsRising Stars Points Progress Column13Rising Stars Points Progress Column15Current Points Progress Rising StarsCurrent Points Progress Vs. TargetCurrent Points Progress Vs. AspirationalColumn18End of Year TargetEnd of Year Target PointsAspirational Progress InputAspirational Progress
3ELPupil 113FKS 53 Sec304 Dev3225 Sec3646 Sec+39.53.59.52.524 Sec+33.55 Beg341
4ELPupil 211MKS 4Entry 1.030Entry 1.0300Entry 1.4311Level 1.03989-8-10Level 1.039Level 1.5411
5ELPupil 37MKS 33 Beg283 Beg2804 Sec3355 Sec+36.53.58.532.53 Sec303 Sec+30.51
6CSPupil 48FKS 33 Sec30Entry 3.136.56.56.51
7ELPupil 511MKS 43 Sec304 Dev3224 Sec3315 Sec+36.53.56.5-0.5-14 Sec+33.55 Beg341
8ELPupil 67MKS 33 Sec304 Dev3224 Sec3315 Sec+36.53.56.5-0.5-14 Sec+33.55 Beg341
9ELPupil 710MKS 43 Sec30Entry 3.136.56.5Entry 3.136.506.5-2.5-4.5Level 1.039Level 1.5411
10ELPupil 87MKS 3P8211 Dev2322 Beg2522 Sec+27.52.56.50.501 Sec+24.52 Beg251
11ELPupil 910MKS 42 Sec+27.53 Dev291.53 Sec3014 Sec+33.53.56-0.5-13 Sec+30.54 Beg311
12ELPupil 108MKS 34 Sec+33.55 Dev351.56 Dev3836 Sec+39.51.561.515 Sec+36.56 Beg371
13CPPupil 114MKS 201 Sec243 Dev2955-1-22 Beg252 Dev261
14CSPupil 1210FKS 4Entry 2.534Entry 3.738441
15ELPupil 136MKS 2P8211 Dev2321 Dev2301 Sec+24.51.53.5-1.5-21 Sec+24.52 Beg251
29ELPupil 2711MKS 45 Sec+36.5Entry 1.331-5.5Entry 2.3343Level 1.03952.500Entry 2.034Entry 2.5341
30ELPupil 289MKS 32 Beg253 Beg2832 Dev26-22 Sec+27.51.52.5-4-4.53 Sec303 Sec+30.51
31ELPupil 2911MKS 4Entry 3.136.5Entry 3.6370.5Entry 3.7381Level 1.03912.5-1-3Level 1.039Level 1.5411
32ELPupil 305MKS 23 Beg283 Beg2803 Sec3023 Sec+30.50.52.50-0.53 Sec303 Sec+30.51
33ELPupil 319FKS 34 Sec+33.55 Dev351.55 Sec3612.5-0.5-15 Sec+36.56 Beg371
34CPPupil 324MKS 22 Sec273 Beg2813 Dev2912-2-2.53 Sec303 Sec+30.51
35CPPupil 334MKS 24 Dev324 Sec3315 Beg3412-0.5-14 Sec+33.55 Beg341
36ELPupil 3411MKS 4Level 2.346Level 1.842-4Level 2.0431Level 2.54852-5-9Level 2.548Level 2.9521
37ELPupil 359MKS 34 Beg314 Dev3214 Sec3312-0.5-14 Sec+33.55 Beg341
38ELPupil 369FKS 36 Dev386 Dev3807 Beg40220.506 Sec+39.57 Beg401
39CSPupil 3713MKS 5Entry 2.835.5Entry 3.5371.51.51
40ELPupil 388MKS 36 Dev386 Sec3916 Sec3906 Sec+39.50.51.5-1-27 Beg407 Dev411
41ELPupil 399MKS 31 Dev231 Dev2301 Dev2301 Sec+24.51.51.5-1.5-21 Sec+24.52 Beg251
42ELPupil 409MKS 32 Dev262 Sec+27.51.51.51
43ELPupil 4111FKS 44 Dev324 Dev3204 Sec3311-0.5-14 Sec+33.55 Beg341
44ELPupil 425MKS 22 Beg252 Beg2502 Dev2611-1-1.52 Sec272 Sec+27.51
45ELPupil 437MKS 36 Beg376 Beg3706 Dev381Entry 3.83801-1-1.56 Sec396 Sec+39.51
51ELPupil 11410MKS 46 Beg377 Dev414Entry 3.537-40-2-3Level 1.039Level 1.4401
56CSPupil 12511MKS 4Entry 2.735Entry 1.833-2-21
57CSPupil 12611MKS 4Level 1.139.5Entry 3.437-2.5-2.51
58ELPupil 12710MKS 46 Sec39Entry 3.136.5-2.5Entry 3.336.50-2.5-2.5-4.5Level 1.039Level 1.5411
59ELPupil 12811MKS 4Entry 2.334Entry 2.63512 Sec+27.5-7.5-6.5-6-8Level 1.541Level 2.0431
60
61Working Below TargetWorking towards TargetTarget MetExceeded Target
62
630.1136363640.2727272730.1136363640.409090909
64
65
660.142857
Original
Cell Formulas
RangeFormula
AE3:AE15,AE29:AE45,AE51,AE56:AE59AE3=SUBTOTAL(103,A3)
A63A63=COUNTIFS(W3:W59,"<=0.4",AE3:AE59,1)/COUNTIFS(B3:B59,"*")
C63C63=COUNTIFS($W$3:$W$59,">=0.5",$W$3:$W$59,"<=1.9")/COUNTIF($B$3:$B$59,"*")
F63F63=COUNTIFS($W$3:$W$59,"=2.0",$W$3:$W$59,"<2.5")/COUNTIF($B$3:$B$59,"*")
I63I63=COUNTIF($W$3:$W$59,">=2.5")/COUNTIF($B$3:$B$59,"*")
A66A66=COUNTIFS(W3:W59,"<=0.4",AE3:AE59,1)/COUNTIFS(B3:B59,"*",AE3:AE59,1)


Not sure if you want the value in A63 or A66
 
Upvote 0
Solution
How about
Fluff.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1PupilEND OF 2020 -2021 LEVELSAutumn 2021Spring 2022Summer 2022ProgressEnd of Year Targets
2SiteNameYear GroupGenderKSColumn1Rising StarsPointsColumn2Rising Stars Points ProgressColumn5 Rising StartsRising Stars Points Progress Column13Rising Stars Points Progress Column15Current Points Progress Rising StarsCurrent Points Progress Vs. TargetCurrent Points Progress Vs. AspirationalColumn18End of Year TargetEnd of Year Target PointsAspirational Progress InputAspirational Progress
3ELPupil 113FKS 53 Sec304 Dev3225 Sec3646 Sec+39.53.59.52.524 Sec+33.55 Beg341
4ELPupil 211MKS 4Entry 1.030Entry 1.0300Entry 1.4311Level 1.03989-8-10Level 1.039Level 1.5411
5ELPupil 37MKS 33 Beg283 Beg2804 Sec3355 Sec+36.53.58.532.53 Sec303 Sec+30.51
6CSPupil 48FKS 33 Sec30Entry 3.136.56.56.51
7ELPupil 511MKS 43 Sec304 Dev3224 Sec3315 Sec+36.53.56.5-0.5-14 Sec+33.55 Beg341
8ELPupil 67MKS 33 Sec304 Dev3224 Sec3315 Sec+36.53.56.5-0.5-14 Sec+33.55 Beg341
9ELPupil 710MKS 43 Sec30Entry 3.136.56.5Entry 3.136.506.5-2.5-4.5Level 1.039Level 1.5411
10ELPupil 87MKS 3P8211 Dev2322 Beg2522 Sec+27.52.56.50.501 Sec+24.52 Beg251
11ELPupil 910MKS 42 Sec+27.53 Dev291.53 Sec3014 Sec+33.53.56-0.5-13 Sec+30.54 Beg311
12ELPupil 108MKS 34 Sec+33.55 Dev351.56 Dev3836 Sec+39.51.561.515 Sec+36.56 Beg371
13CPPupil 114MKS 201 Sec243 Dev2955-1-22 Beg252 Dev261
14CSPupil 1210FKS 4Entry 2.534Entry 3.738441
15ELPupil 136MKS 2P8211 Dev2321 Dev2301 Sec+24.51.53.5-1.5-21 Sec+24.52 Beg251
29ELPupil 2711MKS 45 Sec+36.5Entry 1.331-5.5Entry 2.3343Level 1.03952.500Entry 2.034Entry 2.5341
30ELPupil 289MKS 32 Beg253 Beg2832 Dev26-22 Sec+27.51.52.5-4-4.53 Sec303 Sec+30.51
31ELPupil 2911MKS 4Entry 3.136.5Entry 3.6370.5Entry 3.7381Level 1.03912.5-1-3Level 1.039Level 1.5411
32ELPupil 305MKS 23 Beg283 Beg2803 Sec3023 Sec+30.50.52.50-0.53 Sec303 Sec+30.51
33ELPupil 319FKS 34 Sec+33.55 Dev351.55 Sec3612.5-0.5-15 Sec+36.56 Beg371
34CPPupil 324MKS 22 Sec273 Beg2813 Dev2912-2-2.53 Sec303 Sec+30.51
35CPPupil 334MKS 24 Dev324 Sec3315 Beg3412-0.5-14 Sec+33.55 Beg341
36ELPupil 3411MKS 4Level 2.346Level 1.842-4Level 2.0431Level 2.54852-5-9Level 2.548Level 2.9521
37ELPupil 359MKS 34 Beg314 Dev3214 Sec3312-0.5-14 Sec+33.55 Beg341
38ELPupil 369FKS 36 Dev386 Dev3807 Beg40220.506 Sec+39.57 Beg401
39CSPupil 3713MKS 5Entry 2.835.5Entry 3.5371.51.51
40ELPupil 388MKS 36 Dev386 Sec3916 Sec3906 Sec+39.50.51.5-1-27 Beg407 Dev411
41ELPupil 399MKS 31 Dev231 Dev2301 Dev2301 Sec+24.51.51.5-1.5-21 Sec+24.52 Beg251
42ELPupil 409MKS 32 Dev262 Sec+27.51.51.51
43ELPupil 4111FKS 44 Dev324 Dev3204 Sec3311-0.5-14 Sec+33.55 Beg341
44ELPupil 425MKS 22 Beg252 Beg2502 Dev2611-1-1.52 Sec272 Sec+27.51
45ELPupil 437MKS 36 Beg376 Beg3706 Dev381Entry 3.83801-1-1.56 Sec396 Sec+39.51
51ELPupil 11410MKS 46 Beg377 Dev414Entry 3.537-40-2-3Level 1.039Level 1.4401
56CSPupil 12511MKS 4Entry 2.735Entry 1.833-2-21
57CSPupil 12611MKS 4Level 1.139.5Entry 3.437-2.5-2.51
58ELPupil 12710MKS 46 Sec39Entry 3.136.5-2.5Entry 3.336.50-2.5-2.5-4.5Level 1.039Level 1.5411
59ELPupil 12811MKS 4Entry 2.334Entry 2.63512 Sec+27.5-7.5-6.5-6-8Level 1.541Level 2.0431
60
61Working Below TargetWorking towards TargetTarget MetExceeded Target
62
630.1136363640.2727272730.1136363640.409090909
64
65
660.142857
Original
Cell Formulas
RangeFormula
AE3:AE15,AE29:AE45,AE51,AE56:AE59AE3=SUBTOTAL(103,A3)
A63A63=COUNTIFS(W3:W59,"<=0.4",AE3:AE59,1)/COUNTIFS(B3:B59,"*")
C63C63=COUNTIFS($W$3:$W$59,">=0.5",$W$3:$W$59,"<=1.9")/COUNTIF($B$3:$B$59,"*")
F63F63=COUNTIFS($W$3:$W$59,"=2.0",$W$3:$W$59,"<2.5")/COUNTIF($B$3:$B$59,"*")
I63I63=COUNTIF($W$3:$W$59,">=2.5")/COUNTIF($B$3:$B$59,"*")
A66A66=COUNTIFS(W3:W59,"<=0.4",AE3:AE59,1)/COUNTIFS(B3:B59,"*",AE3:AE59,1)


Not sure if you want the value in A63 or A66

That worked a treat. Thank you so much for that.

Now I need to figure out how to apply that formula to C63 where the calculations are between two numbers.
 
Upvote 0
Almost, you only need this part AF4:AF194,1 once in each countifs
 
Upvote 0

Forum statistics

Threads
1,214,983
Messages
6,122,592
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top