 # 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!