Forum Discussion
Countif function with limited amount of cells to go over
Hi all,
I want to use the countif function, however it only needs to include certain cells and it needs to skip others. The distance between the cells is 3 cells every time, so I want to skip 3 cells then apply the countif function and then apply this function after 3 cells for the whole column. Can anybody help me out?
Cheers
4 Replies
Hi,
To illustrate the idea, for such sample
that could be
=SUMPRODUCT(($B$2:$M$2<10)*(MOD(COLUMN($B$2:$M$2)-COLUMN($B$2),4)=0))
Thanks for the quick response!
However, I cannot get it to work when I have a column of data.
In the attached file I want it to give me back 3, since their are 3 sum functions which give back a value larger than 0.
To count in column you shall change COLUMN on ROW
=SUMPRODUCT(($B$2:$B$13>0)*(MOD(ROW($B$2:$B$13)-ROW($B$2),4)=0))
MOD returns 1 for every fourth cell and 0 otherwise.
($B$2:$B$13>0)
returns 1 if the value in the cell greater than zero and 0 otherwise. They multiplied on each other and sum numbers of cells for which both criteria are met (where we have 1 for both).
In attached file 3 variants of the formula depends on which region you calculate.
