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]

0 is non-blank. There are three non-blank cells. Do you mean non-zero?

 

[6StringJazzer]

This will count non-blank cells from B2:B150. Adjust the top number for your actual maximum row.

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>""))

For non-zero cells:

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>0))

 

[ozbeachbum]

Brilliant both work.
Thanks so much Jeff.
Cheers,
Dave.

 

[ozbeachbum]

This will count non-blank cells from B2:B150. Adjust the top number for your actual maximum row.

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>""))

For non-zero cells:

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>0))

Hi Jeff,
As I indicated your formulae worked perfectly.
I have another worksheet requiring the same formula just a different location, so I copied and pasted the formula to that location on the new sheet
=SUMPRODUCT(--(MOD(ROW(70:322),6)=2)*--((B70:B322)<>0))
but it does not return the correct answer, no matter what I have tried it returns 0.
The sheet is formatted exactly the same.
Any thoughts why this issue would arise.
Cheers,
Dave.

 

[6StringJazzer]

On the new sheet is your data in column B rows 70-322?

 

[6StringJazzer]

My formula depends on the fact that your first row of data was in row 2, so MOD 6 of the row number has to be 2. If you start in row 70, MOD 6 of the row number has to be MOD(70,2) which is 4. So you would have to change the formula to

=SUMPRODUCT(--(MOD(ROW(70:322),6)=4)*--((B70:B322)<>0))

 

[ozbeachbum]

My formula depends on the fact that your first row of data was in row 2, so MOD 6 of the row number has to be 2. If you start in row 70, MOD 6 of the row number has to be MOD(70,2) which is 4. So you would have to change the formula to

=SUMPRODUCT(--(MOD(ROW(70:322),6)=4)*--((B70:B322)<>0))

Hi Jeff,
Once again thank you so much, works perfectly.
I trust that I am not taking up to much of your time.
Prior to contacting you last my thoughts were that the 2 represented the row number so I replaced it with 70 and of course it did not work.
My knowledge base is not that great, if you would be good enough could you explain how MOD(70,2) is 4.
Thanking you once again and I trust I am not putting you to to much trouble.
Cheers,
Dave.

 

[Rick Rothstein]

This will count non-blank cells from B2:B150. Adjust the top number for your actual maximum row.

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>""))

For non-zero cells:

=SUMPRODUCT(--(MOD(ROW(2:150),6)=2)*--((B2:B150)<>0))

Just noting that since you are multiplying the two logical expressions, there is no need to use the double negative signs on each of them. Given that, these two modifications of your formulas will also work...

=SUMPRODUCT((MOD(ROW(2:150),6)=2)*((B2:B150)<>""))

=SUMPRODUCT((MOD(ROW(2:150),6)=2)*((B2:B150)<>0))

 

[6StringJazzer]

Thanks Rick. I often just put in the "--" out of laziness so I don't have to stop and think about whether they are needed