Counting specific values in every 4th row

Dainer

New Member
Joined
Dec 25, 2017
Messages
5
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>
 

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.
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)
 
Upvote 0
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.
 
Upvote 0
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) ?
 
Upvote 0
Tried, I think for number "2" it should be ($B$2:$H$12=2;1)
Otherwise it counts each "2" twice.
Correct?
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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