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

2)	1	2	4	5	6	7	11
3)	35	43	52	61	66
4)	10	17	23	24	25	29	32
5)
6)	1	2	3	4	13	14	16
7)	15	32	33	47	63
8)	5	8	9	21	24	43	44
9)
10)	1	3	7	11	15	19	20
11)	10	18	25	28	31
12)	4	9	12	24	27	32	36

<colgroup><col style="mso-width-source:userset;mso-width-alt:1462; width:30pt" span="8" width="40"> </colgroup><tbody>
</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...