Count Data in Range increment by 5 columns

[bdtran]

Hi All,

I encountered a situation where I need to create a formula get the result as in the Row5 by counting how many #5 in row3 range increment by 5 columns for the next result.

example below:

B5=COUNTIF($B3:F3,"5")
C5=COUNTIF($B3:K3,"5")
D5=COUNTIF($B3:P3,"5")
E5=COUNTIF($B3:U3,"5")

Is there a way to create a formula in B5 and drag along get the result automatically.

Thanks,
*BDT


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Data[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Count how many "5" every 5 columns increment[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Try this in B5 and copy rightwards.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIFS(OFFSET($B$3,,,,COLUMNS($B$5:B5)*5),5)[/FONT]