Calculate the number of non blank cells every nth row in a column

[ozbeachbum]

Hi All,
I am trying to calculate the number of non blank cells every nth (6th) row in a column, cells are formatted as number.
EG: Cell B2=18; B8=0, B14=33 Answer Required 2. Two non blank cells.
Any assistance would be greatly appreciated.
Dave.

 

[6StringJazzer]

if you would be good enough could you explain how MOD(70,2) is 4.

That is an error. It should have said MOD(70,6) = 4. My actual formula is correct, but this explanation had an error.

 

[ozbeachbum]

Thanks again Jeff you have been so helpful.
I feel I am putting you to a lot of trouble.
As I said my ability to build formulas is not that great and I am trying to understand how to adjust the formula if I require it in another position as the =# changes dependent on position.
Can you provide me with an explanation of how you ascertain the =#.
Thank you again for your assistance and input.
Dave.

 

[6StringJazzer]

To determine the equalnumber put the following formula in any cell:

=MOD(firstrow, skipsize)

where

firstrow is the number of the first row of data
skipsize is the number of rows to skip each time, 6 in your case

and the result will be your equalnumber.

Then your formula will be

=SUMPRODUCT(--(MOD(ROW(firstrow:lastrow),skipsize)=equalnumber)*--((Bfirstrow:Blastrow)<>0))

I hope that covers it

 

[ozbeachbum]

You are going to think me totally stupid.
The information you provided is well laid out and shows what the positions represent.
What I need to know is how you arrive at the =equalnumber.

The formula you first provided was =2
=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>0))

When I changed position it became =4
=SUMPRODUCT(--(MOD(ROW(70:322),6)=4)*--((P70:P322)<>0))

Should I need to use the formula in another position say
=SUMPRODUCT(--(MOD(ROW(20:322),6)=?)*--((P70:P322)<>0))

How do I work out what the =equalnumber should be.
I am looking at it mathematically but can't see an answer.
Thank you so much for your time and effort.

 

[6StringJazzer]

That's the first thing I described in post #6.

If your data starts on row 2, MOD(2,6) = 2 because 2 divided by 6 is 0 with a remainder of 2. So every 6th row is going to have a remainder of 2 when you divide by 6.

$scratch.xlsm
	A	B
1	Row Number	Remainder when divding by 6
2	2	2
3	3	3
4	4	4
5	5	5
6	6	0
7	7	1
8	8	2
9	9	3
10	10	4
11	11	5
12	12	0
13	13	1
14	14	2
15	15	3
16	16	4
17	17	5
18	18	0
19	19	1
20	20	2
21	21	3
22	22	4
Sheet13

Cell Formulas
Range	Formula
B2:B22	B2	=MOD(A2,6)
A3:A22	A3	=A2+1

If your data starts on row 70, you need to select rows that have that remainder. MOD(70,6) = 4, because 70 divided by 6 is 11 with remainder 4.

$scratch.xlsm
	A	B
1	Row Number	Remainder when divding by 6
2	70	4
3	71	5
4	72	0
5	73	1
6	74	2
7	75	3
8	76	4
9	77	5
10	78	0
11	79	1
12	80	2
13	81	3
14	82	4
15	83	5
16	84	0
17	85	1
18	86	2
19	87	3
20	88	4
21	89	5
22	90	0
Sheet13

Cell Formulas
Range	Formula
B2:B22	B2	=MOD(A2,6)
A3:A22	A3	=A2+1

If your data starts on row 20, you need to select rows that have that remainder. MOD(20,6) = 2, because 20 divided by 6 is 3 with remainder 2.

$scratch.xlsm
	A	B
1	Row Number	Remainder when divding by 6
2	20	2
3	21	3
4	22	4
5	23	5
6	24	0
7	25	1
8	26	2
9	27	3
10	28	4
11	29	5
12	30	0
13	31	1
14	32	2
15	33	3
16	34	4
17	35	5
18	36	0
19	37	1
20	38	2
21	39	3
22	40	4
Sheet13

Cell Formulas
Range	Formula
B2:B22	B2	=MOD(A2,6)
A3:A22	A3	=A2+1

 

[ozbeachbum]

Thank you so much for your time and effort with a novice.
I did not know that the MOD(A70,6) was a division, however I did do that manually and as I got 11.66, I couldn't see how that was applicable.
I never thought to use that part of the formula to get the answer.
Now that you show it in such detail, I have finally got it and will leave you in peace.
Thanks once again, your are a champion.
Cheers,
Dave.

 

[6StringJazzer]

Not MOD(A70,6), just MOD(70,6)

MOD is not itself a division, it gives you the remainder of integer division. 70/6 is 11.66 but you have to do integer division. 70/6 is 11 with remainder 4. The biggest divisor of 6 into 70 is 11. That gives 66. So you have 4 left over.

I don't mean to be condescending but this is grade-school arithmetic. Please see Division and Remainders for more of a description.