Counting specific values in every 4th row

[Dainer]

Hello

I need help in finding the formula to count specific values in every 4th row.
For example, I need to count how many numbers "1" are in row 2), 6) and 10) total (in this case result should be "3")
Later I need to do the same thing separately for numbers 2...75.
Counting how many number "1" including every row was simple.
Why I need automation for every 4th row is, that my table consists of nearly 1000 rows.

Thank you in advance for helping!

Example table below:

B C D E F G H

<colgroup><col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="8" width="40"> </colgroup><tbody>
[TD="class: xl65, width: 40, align: right"]2)
[/TD]
[TD="class: xl67, width: 40, align: right"]1
[/TD]
[TD="class: xl68, width: 40, align: right"]2[/TD]
[TD="class: xl68, width: 40, align: right"]4[/TD]
[TD="class: xl68, width: 40, align: right"]5[/TD]
[TD="class: xl68, width: 40, align: right"]6[/TD]
[TD="class: xl68, width: 40, align: right"]7[/TD]
[TD="class: xl68, width: 40, align: right"]11[/TD]

[TD="align: right"] 3)
[/TD]
[TD="class: xl69, align: right"]35[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]66[/TD]

[TD="align: right"] 4)
[/TD]
[TD="class: xl70, align: right"]10[/TD]
[TD="class: xl66, align: right"]17[/TD]
[TD="class: xl66, align: right"]23[/TD]
[TD="class: xl66, align: right"]24[/TD]
[TD="class: xl66, align: right"]25[/TD]
[TD="class: xl66, align: right"]29[/TD]
[TD="class: xl66, align: right"]32[/TD]

[TD="align: right"] 5)
[/TD]

[TD="class: xl65, align: right"]6)
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[TD="class: xl68, align: right"]2[/TD]
[TD="class: xl68, align: right"]3[/TD]
[TD="class: xl68, align: right"]4[/TD]
[TD="class: xl68, align: right"]13[/TD]
[TD="class: xl68, align: right"]14[/TD]
[TD="class: xl68, align: right"]16[/TD]

[TD="align: right"]7)
[/TD]
[TD="class: xl69, align: right"]15
[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]63[/TD]

[TD="align: right"]8)
[/TD]
[TD="class: xl70, align: right"]5
[/TD]
[TD="class: xl66, align: right"]8[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl66, align: right"]21[/TD]
[TD="class: xl66, align: right"]24[/TD]
[TD="class: xl66, align: right"]43[/TD]
[TD="class: xl66, align: right"]44[/TD]

[TD="align: right"]9)
[/TD]

[TD="class: xl71, align: right"]10)
[/TD]
[TD="class: xl67, align: right"]1
[/TD]
[TD="class: xl68, align: right"]3[/TD]
[TD="class: xl68, align: right"]7[/TD]
[TD="class: xl68, align: right"]11[/TD]
[TD="class: xl68, align: right"]15[/TD]
[TD="class: xl68, align: right"]19[/TD]
[TD="class: xl68, align: right"]20[/TD]

[TD="align: right"]11)
[/TD]
[TD="class: xl69, align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]12)
[/TD]
[TD="class: xl70, align: right"]4
[/TD]
[TD="class: xl66, align: right"]9[/TD]
[TD="class: xl66, align: right"]12[/TD]
[TD="class: xl66, align: right"]24[/TD]
[TD="class: xl66, align: right"]27[/TD]
[TD="class: xl66, align: right"]32[/TD]
[TD="class: xl66, align: right"]36[/TD]

</tbody>

 

[Aladin Akyurek]

@Dainer

Control+shift+enter, not just enter:

=SUM(IF(MOD(ROW($B$2:$H$12)-ROW($B$2),4)=0,IF($B$2:$H$12=1,1)))

 

[Dainer]

Thank you for quick reply.

It returns with pop-up error window (i pressed ctrl+shift+enter) also tried just enter, same effect.
After clicking ok, it highlights ",4" part of the formula (if that helps)

 

[Aladin Akyurek]

Thank you for quick reply.

It returns with pop-up error window (i pressed ctrl+shift+enter) also tried just enter, same effect.
After clicking ok, it highlights ",4" part of the formula (if that helps)

=SUM(IF(MOD(ROW($B$2:$H$12)-ROW($B$2),4)=0,IF($B$2:$H$12=1,1)))

must be confirmed with control+shift+enter, which means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself puts a pair of { and } around the formula in recognition.

If you are on a non-American system, you might need to replace comma's with semi-colons.​

 

[Dainer]

changing , to ; worked. Thanks.

Now to do the same thing for other numbers I simply change number "1" to number "n"?
In case of number "2" ...($B$2:$H$12=1;1) becomes ...($B$2:$H$12=2;2) ?

 

[Dainer]

Tried, I think for number "2" it should be ($B$2:$H$12=2;1)
Otherwise it counts each "2" twice.
Correct?

 

[Aladin Akyurek]

changing , to ; worked. Thanks.

Now to do the same thing for other numbers I simply change number "1" to number "n"?
In case of number "2" ...($B$2:$H$12=1;1) becomes ...($B$2:$H$12=2;2) ?

Last 1 is returned whenever the two conditions are met and the outer SUM sums those 1's.

Tried, I think for number "2" it should be ($B$2:$H$12=2;1)
Otherwise it counts each "2" twice.
Correct?

Correct...