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 c...
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.
- Thanks, that worked for me!