Select every other row using formula

  • Hey everyone!


    Just trying to make my life easier, Is there a formula I could do that would take every other cell in a range?


    Right now I am doing a formula like: If(CountIf(A2,A4,A6,A8)=4,"T","F") but I have a lot more cells than that and a lot of ranges. Is there a way to do something like A2:A8 offset by 1 to get the same result?



    Thanks ahead of time. I tried search first to no avail, so if anyone has link, that would be great also. Thanks!

  • Re: Select every other row using formula


    One way would be to use SUMPRODUCT as a count function. If you want to count the non-empty cells in A1:A36 in the even rows only, use:
    =SUMPRODUCT((NOT(ISBLANK(A1:A36)))*(MOD(ROW(A1:A36),2)=0))

  • Re: Select every other row using formula


    Thanks Thomach, this is great, but tthe problem I am having is I need it to count the first cell, but I modified your code slightly:


    =SUMPRODUCT((NOT(ISBLANK(A1:A36)))*(MOD(ROW(A1:A36)+1,2)=0))


    I just added a +1 to the end of the Mod, and it works perfectly. Thanks for the help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!