Count zero formula values in column, excluding any second zero occurrences 2 rows below

Ironman

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
Hi and a Happy New Year to you!

The formula in cell B262 is supposed to count the weeks rows 2, 7, 12, 17, 22, 27, 32....down to row 257 that contain a zero value. The correct result should be 22. However, the formula/result are incorrect because the formula sums all the zeros in the column and therefore also includes year to date averages 2 rows below those containing the zeros, which I don't want.

I'd be grateful for a formula that can do this.

Many thanks!
test.xlsx
AB
1WEEK2021
210
3RK/381
4YTD AVE0
5RK/381
6
720
8RK/381
9YTD AVE0
10RK/381
11
1230
13RK/381
14YTD AVE0
15RK/381
16
1740
18RK/381
19YTD AVE0
20RK/381
21
2250
23RK/381
24YTD AVE0
25RK/381
26
2760
28RK/381
29YTD AVE0
30RK/381
31
3270
33RK/381
34YTD AVE0
35RK/381
36
3780
38RK/381
39YTD AVE0
40RK/381
41
4290
43RK/381
44YTD AVE0
45RK/381
46
47100
48RK/381
49YTD AVE0
50RK/381
51
52110
53RK/381
54YTD AVE0
55RK/381
56
57120
58RK/381
59YTD AVE0
60RK/381
61
62130
63RK/381
64YTD AVE0
65RK/381
66
67140
68RK/381
69YTD AVE0
70RK/381
71
72150
73RK/381
74YTD AVE0
75RK/381
76
77160
78RK/381
79YTD AVE0
80RK/381
81
82170
83RK/381
84YTD AVE0
85RK/381
86
87180
88RK/381
89YTD AVE0
90RK/381
91
92190
93RK/381
94YTD AVE0
95RK/381
96
97200
98RK/381
99YTD AVE0
100RK/381
101
102210
103RK/381
104YTD AVE0
105RK/381
106
107220
108RK/381
109YTD AVE0
110RK/381
111
112238
113RK/381
114YTD AVE0
115RK/381
116
117246
118RK/381
119YTD AVE1
120RK/381
121
1222519
123RK/381
124YTD AVE1
125RK/381
126
1272623
128RK/381
129YTD AVE2
130RK/381
131
132276
133RK/381
134YTD AVE2
135RK/381
136
1372818
138RK/381
139YTD AVE3
140RK/381
141
1422919
143RK/381
144YTD AVE3
145RK/381
146
1473021
148RK/381
149YTD AVE4
150RK/381
151
1523115
153RK/381
154YTD AVE4
155RK/381
156
1573222
158RK/381
159YTD AVE5
160RK/381
161
1623322
163RK/381
164YTD AVE5
165RK/381
166
1673426
168RK/381
169YTD AVE6
170RK/381
171
1723511
173RK/381
174YTD AVE6
175RK/381
176
1773626
178RK/381
179YTD AVE7
180RK/381
181
1823728
183RK/381
184YTD AVE7
185RK/381
186
1873826
188RK/381
189YTD AVE8
190RK/381
191
1923913
193RK/381
194YTD AVE8
195RK/381
196
1974021
198RK/381
199YTD AVE8
200RK/381
201
2024117
203RK/381
204YTD AVE8
205RK/381
206
2074221
208RK/381
209YTD AVE9
210RK/381
211
2124322
213RK/381
214YTD AVE9
215RK/381
216
2174426
218RK/381
219YTD AVE9
220RK/381
221
2224530
223RK/381
224YTD AVE10
225RK/381
226
2274621
228RK/381
229YTD AVE10
230RK/381
231
2324727
233RK/381
234YTD AVE10
235RK/381
236
2374821
238RK/381
239YTD AVE11
240RK/381
241
2424917
243RK/381
244YTD AVE11
245RK/381
246
2475015
248RK/381
249YTD AVE11
250RK/381
251
2525128
253RK/381
254YTD AVE11
255RK/381
256
2575227
258RK/381
2595327
260AVES12
261RK/381
2620 WKS44
Weekly Tracking
Cell Formulas
RangeFormula
A3,A261,A258,A255,A253,A250,A248,A245,A243,A240,A238,A235,A233,A230,A228,A225,A223,A220,A218,A215,A213,A210,A208,A205,A203,A200,A198,A195,A193,A190,A188,A185,A183,A180,A178,A175,A173,A170,A168,A165,A163,A160,A158,A155,A153,A150,A148,A145,A143,A140,A138,A135A3="RK/"&YearEndRank-3
B2B2=IF(COUNTA('Daily Tracking'!AO5:AO11),SUM('Daily Tracking'!AO5:AO11),"")
B3B3=IF(B2="","",RANK(B2,$B$2:$AP$2))
B4B4=IF(B2="","",B2)
B5,B255,B250,B245,B240,B235,B230,B225,B220,B215,B210,B205,B200,B195,B190,B185,B180,B175,B170,B165,B160,B155,B150,B145,B140,B135,B130,B125,B120,B115,B110,B105,B100,B95,B90,B85,B80,B75,B70,B65,B60,B55,B50,B45,B40,B35,B30,B25,B20,B15,B10B5=IF(B4="","",RANK(B4,$B4:$AP4))
B7B7=IF(COUNTA('Daily Tracking'!AO12:AO18),SUM('Daily Tracking'!AO12:AO18),"")
B8B8=IF(B7="","",RANK(B7,$B$7:$AP$7))
B9,B254,B249,B244,B239,B234,B229,B224,B219,B214,B209,B204,B199,B194,B189,B184,B179,B174,B169,B164,B159,B154,B149,B144,B139,B134,B129,B124,B119,B114,B109,B104,B99,B94,B89,B84,B79,B74,B69,B64,B59,B54,B49,B44,B39,B34,B29,B24,B19,B14B9=IF(B7="","",AVERAGE(IF(MOD(ROW(B$1:B7),5)=2,B$1:B7)))
B12B12=IF(COUNTA('Daily Tracking'!AO19:AO25),SUM('Daily Tracking'!AO19:AO25),"")
B13B13=IF(B12="","",RANK(B12,$B$12:$AP$12))
B17B17=IF(COUNTA('Daily Tracking'!AO26:AO32),SUM('Daily Tracking'!AO26:AO32),"")
B18B18=IF(B17="","",RANK(B17,$B$17:$AP$17))
B22B22=IF(COUNTA('Daily Tracking'!AO33:AO39),SUM('Daily Tracking'!AO33:AO39),"")
B23B23=IF(B22="","",RANK(B22,$B$22:$AP$22))
B27B27=IF(COUNTA('Daily Tracking'!AO40:AO46),SUM('Daily Tracking'!AO40:AO46),"")
B28B28=IF(B27="","",RANK(B27,$B$27:$AP$27))
B32B32=IF(COUNTA('Daily Tracking'!AO47:AO53),SUM('Daily Tracking'!AO47:AO53),"")
B33B33=IF(B32="","",RANK(B32,$B$32:$AP$32))
B37B37=IF(COUNTA('Daily Tracking'!AO54:AO60),SUM('Daily Tracking'!AO54:AO60),"")
B38B38=IF(B37="","",RANK(B37,$B$37:$AP$37))
B42B42=IF(COUNTA('Daily Tracking'!AO61:AO68),SUM('Daily Tracking'!AO61:AO68),"")
B43B43=IF(B42="","",RANK(B42,$B$42:$AP$42))
B47B47=IF(COUNTA('Daily Tracking'!AO69:AO75),SUM('Daily Tracking'!AO69:AO75),"")
B48B48=IF(B47="","",RANK(B47,$B$47:$AP$47))
B52B52=IF(COUNTA('Daily Tracking'!AO76:AO82),SUM('Daily Tracking'!AO76:AO82),"")
B53B53=IF(B52="","",RANK(B52,$B$52:$AP$52))
B57B57=IF(COUNTA('Daily Tracking'!AO83:AO89),SUM('Daily Tracking'!AO83:AO89),"")
B58B58=IF(B57="","",RANK(B57,$B$57:$AP$57))
B62B62=IF(COUNTA('Daily Tracking'!AO90:AO96),SUM('Daily Tracking'!AO90:AO96),"")
B63B63=IF(B62="","",RANK(B62,$B$62:$AP$62))
B67B67=IF(COUNTA('Daily Tracking'!AO97:AO103),SUM('Daily Tracking'!AO97:AO103),"")
B68B68=IF(B67="","",RANK(B67,$B$67:$AP$67))
B72B72=IF(COUNTA('Daily Tracking'!AO104:AO110),SUM('Daily Tracking'!AO104:AO110),"")
B73B73=IF(B72="","",RANK(B72,$B$72:$AP$72))
B77B77=IF(COUNTA('Daily Tracking'!AO111:AO117),SUM('Daily Tracking'!AO111:AO117),"")
B78B78=IF(B77="","",RANK(B77,$B$77:$AP$77))
B82B82=IF(COUNTA('Daily Tracking'!AO118:AO124),SUM('Daily Tracking'!AO118:AO124),"")
B83B83=IF(B82="","",RANK(B82,$B$82:$AP$82))
B87B87=IF(COUNTA('Daily Tracking'!AO125:AO131),SUM('Daily Tracking'!AO125:AO131),"")
B88B88=IF(B87="","",RANK(B87,$B$87:$AP$87))
B92B92=IF(COUNTA('Daily Tracking'!AO132:AO138),SUM('Daily Tracking'!AO132:AO138),"")
B93B93=IF(B92="","",RANK(B92,$B$92:$AP$92))
B97B97=IF(COUNTA('Daily Tracking'!AO139:AO145),SUM('Daily Tracking'!AO139:AO145),"")
B98B98=IF(B97="","",RANK(B97,$B$97:$AP$97))
B102B102=IF(COUNTA('Daily Tracking'!AO146:AO152),SUM('Daily Tracking'!AO146:AO152),"")
B103B103=IF(B102="","",RANK(B102,$B$102:$AP$102))
B107B107=IF(COUNTA('Daily Tracking'!AO153:AO159),SUM('Daily Tracking'!AO153:AO159),"")
B108B108=IF(B107="","",RANK(B107,$B$107:$AP$107))
B112B112=IF(COUNTA('Daily Tracking'!AO160:AO166),SUM('Daily Tracking'!AO160:AO166),"")
B113B113=IF(B112="","",RANK(B112,$B$112:$AP$112))
B117B117=IF(COUNTA('Daily Tracking'!AO167:AO173),SUM('Daily Tracking'!AO167:AO173),"")
B118B118=IF(B117="","",RANK(B117,$B$117:$AP$117))
B122B122=IF(COUNTA('Daily Tracking'!AO174:AO180),SUM('Daily Tracking'!AO174:AO180),"")
B123B123=IF(B122="","",RANK(B122,$B$122:$AP$122))
B127B127=IF(COUNTA('Daily Tracking'!AO181:AO187),SUM('Daily Tracking'!AO181:AO187),"")
B128B128=IF(B127="","",RANK(B127,$B$127:$AP127))
B132B132=IF(COUNTA('Daily Tracking'!AO188:AO194),SUM('Daily Tracking'!AO188:AO194),"")
B133B133=IF(B132="","",RANK(B132,$B$132:$AP$132))
B137B137=IF(COUNTA('Daily Tracking'!AO195:AO201),SUM('Daily Tracking'!AO195:AO201),"")
B138B138=IF(B137="","",RANK(B137,$B$137:$AP$137))
B142B142=IF(COUNTA('Daily Tracking'!AO202:AO208),SUM('Daily Tracking'!AO202:AO208),"")
B143B143=IF(B142="","",RANK(B142,$B$142:$AP$142))
B147B147=IF(COUNTA('Daily Tracking'!AO209:AO215),SUM('Daily Tracking'!AO209:AO215),"")
B148B148=IF(B147="","",RANK(B147,$B$147:$AP$147))
B152B152=IF(COUNTA('Daily Tracking'!AO216:AO222),SUM('Daily Tracking'!AO216:AO222),"")
B153B153=IF(B152="","",RANK(B152,$B$152:$AP$152))
B157B157=IF(COUNTA('Daily Tracking'!AO223:AO229),SUM('Daily Tracking'!AO223:AO229),"")
B158B158=IF(B157="","",RANK(B157,$B$157:$AP$157))
B162B162=IF(COUNTA('Daily Tracking'!AO230:AO236),SUM('Daily Tracking'!AO230:AO236),"")
B163B163=IF(B162="","",RANK(B162,$B$162:$AP$162))
B167B167=IF(COUNTA('Daily Tracking'!AO237:AO243),SUM('Daily Tracking'!AO237:AO243),"")
B168B168=IF(B167="","",RANK(B167,$B$167:$AP$167))
B172B172=IF(COUNTA('Daily Tracking'!AO244:AO250),SUM('Daily Tracking'!AO244:AO250),"")
B173B173=IF(B172="","",RANK(B172,$B$172:$AP$172))
B177B177=IF(COUNTA('Daily Tracking'!AO251:AO257),SUM('Daily Tracking'!AO251:AO257),"")
B178B178=IF(B177="","",RANK(B177,$B$177:$AP$177))
B182B182=IF(COUNTA('Daily Tracking'!AO258:AO264),SUM('Daily Tracking'!AO258:AO264),"")
B183B183=IF(B182="","",RANK(B182,$B$182:$AP$182))
B187B187=IF(COUNTA('Daily Tracking'!AO265:AO271),SUM('Daily Tracking'!AO265:AO271),"")
B188B188=IF(B187="","",RANK(B187,$B$187:$AP$187))
B192B192=IF(COUNTA('Daily Tracking'!AO272:AO278),SUM('Daily Tracking'!AO272:AO278),"")
B193B193=IF(B192="","",RANK(B192,$B$192:$AP$192))
B197B197=IF(COUNTA('Daily Tracking'!AO279:AO285),SUM('Daily Tracking'!AO279:AO285),"")
B198B198=IF(B197="","",RANK(B197,$B$197:$AP$197))
B202B202=IF(COUNTA('Daily Tracking'!AO286:AO292),SUM('Daily Tracking'!AO286:AO292),"")
B203B203=IF(B202="","",RANK(B202,$B$202:$AP$202))
B207B207=IF(COUNTA('Daily Tracking'!AO293:AO299),SUM('Daily Tracking'!AO293:AO299),"")
B208B208=IF(B207="","",RANK(B207,$B$207:$AP$207))
B212B212=IF(COUNTA('Daily Tracking'!AO300:AO306),SUM('Daily Tracking'!AO300:AO306),"")
B213B213=IF(B212="","",RANK(B212,$B$212:$AP$212))
B217B217=IF(COUNTA('Daily Tracking'!AO307:AO313),SUM('Daily Tracking'!AO307:AO313),"")
B218B218=IF(B217="","",RANK(B217,$B$217:$AP$217))
B222B222=IF(COUNTA('Daily Tracking'!AO314:AO320),SUM('Daily Tracking'!AO314:AO320),"")
B223B223=IF(B222="","",RANK(B222,$B$222:$AP$222))
B227B227=IF(COUNTA('Daily Tracking'!AO321:AO327),SUM('Daily Tracking'!AO321:AO327),"")
B228B228=IF(B227="","",RANK(B227,$B$227:$AP$227))
B232B232=IF(COUNTA('Daily Tracking'!AO328:AO334),SUM('Daily Tracking'!AO328:AO334),"")
B233B233=IF(B232="","",RANK(B232,$B$232:$AP$232))
B237B237=IF(COUNTA('Daily Tracking'!AO335:AO341),SUM('Daily Tracking'!AO335:AO341),"")
B238B238=IF(B237="","",RANK(B237,$B$237:$AP$237))
B242B242=IF(COUNTA('Daily Tracking'!AO342:AO348),SUM('Daily Tracking'!AO342:AO348),"")
B243B243=IF(B242="","",RANK(B242,$B$242:$AP$242))
B247B247=IF(COUNTA('Daily Tracking'!AO349:AO355),SUM('Daily Tracking'!AO349:AO355),"")
B248B248=IF(B247="","",RANK(B247,$B$247:$AP$247))
B252B252=IF(COUNTA('Daily Tracking'!AO356:AO362),SUM('Daily Tracking'!AO356:AO362),"")
B253B253=IF(B252="","",RANK(B252,$B$252:$AP$252))
B257B257=IF(COUNTA('Daily Tracking'!AO363:AO367)+COUNTA('Daily Tracking'!AP2:AP3),SUM('Daily Tracking'!AO363:AO367,'Daily Tracking'!AP2:AP3),"")
B258B258=IF(B257="","",RANK(B257,$B$257:$AP$257))
B259B259=IF(COUNTA('Daily Tracking'!AO364:AO367)+COUNTA('Daily Tracking'!AP2:AP4),SUM('Daily Tracking'!AO364:AO367,'Daily Tracking'!AP2:AP4),"")
B260B260=IF(B2="","",AVERAGE(IF(MOD(ROW(B1:B257),5)=2,B1:B257),B259))
B261B261=IF(B260="","",RANK(B260,$B$260:$AP$260))
B262B262=(COUNTIF(B2:B257,"0"))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
EntRng=OFFSET('Daily Tracking'!$Y$2:$Y$367,0,YEAR(TODAY())-2005)B259, B257
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B259:C259Expression=IF(OR(B259<39.5,B259=""),1,0)textYES
B259:C259Cell Valuebetween 39.5 and 44.9textYES
B259:C259Cell Valuebetween 45 and 49.9textYES
B259:C259Cell Valuebetween 39.5 and 44.9textYES
B259:C259Cell Valuebetween 45 and 49.9textYES
B260:C260Cell Valuebetween 39.5 and 44.9textYES
B260:C260Cell Valuebetween 45 and 49.9textYES
B260:C260Cell Value>=50textYES
B262:C262Cell Value=0textYES
B261:C261Cell Value=1textYES
B261:C261Cell Value=2textYES
B261:C261Cell Value=3textYES
B252:C252,B257:C257Expression=IF(OR(B252<39.5,B252=""),1,0)textYES
B252:C252,B257:C257Cell Valuebetween 39.5 and 44.9textYES
B252:C252,B257:C257Cell Valuebetween 45 and 49.9textYES
B252:C252,B257:C257Cell Valuebetween 39.5 and 44.9textYES
B252:C252,B257:C257Cell Valuebetween 45 and 49.9textYES
B255:C255,B253:C253,B258:C258Cell Value=1textYES
B255:C255,B253:C253,B258:C258Cell Value=2textYES
B255:C255,B253:C253,B258:C258Cell Value=3textYES
B254:C254Expression=IF(OR(B254<39.5,B254=""),1,0)textYES
B254:C254Cell Valuebetween 39.5 and 44.9textYES
B254:C254Cell Valuebetween 45 and 49.9textYES
B254:C254Cell Valuebetween 39.5 and 44.9textYES
B254:C254Cell Valuebetween 45 and 49.9textYES
A7Cell Valuebetween 40 and 45textYES
A7Cell Valuebetween 46 and 50textYES
A7Cell Value>=51textYES
A2,AS2:AT2Cell Valuebetween 40 and 45textYES
A2,AS2:AT2Cell Valuebetween 46 and 50textYES
A2,AS2:AT2Cell Value>=51textYES
AR263,AR265,AR261,A265,A263,A261,D258:AP258,D253:AP253Cell Value=1textYES
AR263,AR265,AR261,A265,A263,A261,D258:AP258,D253:AP253Cell Value=2textYES
AR263,AR265,AR261,A265,A263,A261,D258:AP258,D253:AP253Cell Value=3textYES
AS197:AT197,AS202:AT202,AS207:AT207,AS212:AT212,AS217:AT217,AS222:AT222,AS227:AT227,AS232:AT232,AS242:AT242,AS237:AT237,AS247:AT247,AS252:AT252,AS257:AT257,AS259:AT260,AS9:AT9,AS14:AT14,AS19:AT19,AS24:AT24,AS29:AT29,AS34:AT34,AS39:AT39,AS49:AT49,AS54:AT54Cell Valuebetween 40 and 45textYES
AS197:AT197,AS202:AT202,AS207:AT207,AS212:AT212,AS217:AT217,AS222:AT222,AS227:AT227,AS232:AT232,AS242:AT242,AS237:AT237,AS247:AT247,AS252:AT252,AS257:AT257,AS259:AT260,AS9:AT9,AS14:AT14,AS19:AT19,AS24:AT24,AS29:AT29,AS34:AT34,AS39:AT39,AS49:AT49,AS54:AT54Cell Valuebetween 46 and 50textYES
AS197:AT197,AS202:AT202,AS207:AT207,AS212:AT212,AS217:AT217,AS222:AT222,AS227:AT227,AS232:AT232,AS242:AT242,AS237:AT237,AS247:AT247,AS252:AT252,AS257:AT257,AS259:AT260,AS9:AT9,AS14:AT14,AS19:AT19,AS24:AT24,AS29:AT29,AS34:AT34,AS39:AT39,AS49:AT49,AS54:AT54Cell Value>=51textYES
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
How about
Excel Formula: 
=SUMPRODUCT((ISNUMBER(A2:A261))*(B2:B261=0))
 
Upvote 0
Solution
Ahhhh that's just perfect! Thanks ever so much Fluff, and a Happy New Year to you!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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