Formula to select ranges for counting

[tomas]

Hello

I know this thing could be done by VBA but I am interested if this can be done with formulas.

So in sample file I want to select every second column from start and count how many numbers are under 10. The result should be in one cell. And you cant select columns to count manually.

I thoght about some crazy index with array of solution nested into countif but did not work

 

[Luke M]

Write it in as a criteria. Check that the column number is even, and that the number is less than 10.
=SUMPRODUCT((MOD(COLUMN(A2:H7),2)=0)*(A2:H7<10))

If you wanted the odd numbered columns, change the bold part to be
=1

 

[tomas]

Thanks Luke. I understand the formula just wasn't able to come up with it