countifs every 3rd row in a specified column.

[lambi153]

Hi,

I have data in the following range (B359:B406), I need to count the number of times a 0 might occur but only in the cells B361,B364,B367,B370 (every 3rd row) finishing with B406.

I have tried

=SUMPRODUCT(--(MOD(ROW(B359:B406)-ROW(B361),3)=0),--(B359:B406="0"))

but it only returns 0 no matter how many of the cells I'm looking at have a 0 in them.

Any help would be most appreciated.

J

 

[AlanY]

the problem is the --(B359:B406="0") in your formula, it's looking for a 0 in text.

what are the typically data in the other cells?

 

[AlanY]

you can try this instead

=SUMPRODUCT(--(MOD(ROW(B359:B406)-ROW(B361),3)=0),--(B359:B406=0),--(LEN(B359:B406)>0))

 

[lambi153]

you can try this instead

=SUMPRODUCT(--(MOD(ROW(B359:B406)-ROW(B361),3)=0),--(B359:B406=0),--(LEN(B359:B406)>0))

Hi Alan,

Thank you for coming back to me. in the other 2 cells there are numbers but no zero's. I;m coming to try your formula now.

J

 

[lambi153]

It worked perfectly.... Thank you.

 

[AlanY]

you're welcome

 

[Aladin Akyurek]

This should do it also...

=SUMPRODUCT(--(MOD(ROW(B359:B406)-ROW(B361),3)=0),--ISNUMBER(1/B359:B406))