Countif Question

[JimS]

=COUNTIF(R12:R1008,">0")

Instead of this formula I want it to count every fourth cell. So
starting with R12, then it would count r16,r20, r24, etc.

How do I do that?

Thanks

 

[T. Valko]

Assuming there's no text in any of the target cells...

=SUMPRODUCT(--(MOD(ROW(R12:R1008)-ROW(R12),4)=0),--(R12:R1008>0))

If there might be text in some of the target cells:

=SUMPRODUCT(--(MOD(ROW(R12:R1008)-ROW(R12),4)=0),--(ISNUMBER(R12:R1008)),--(R12:R1008>0))

Note that this formula is robust against row insertions *before* the
referenced range. However, if you insert new rows *within* the range then
that changes the cell interval and you could get an incorrect result.

 

[JimS]

Thanks, Biff.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks, Biff.

 

[JimS]

How would I modify the second formula if I wanted to sum the amounts
in every fourth cell in stead of counting them?

 

[T. Valko]

The sum version will ignore text so we can remove the ISNUMBER test:

=SUMPRODUCT(--(MOD(ROW(R12:R1008)-ROW(R12),4)=0),--(R12:R1008>0),R12:R1008)

 

[JimS]

Thanks again, Biff. I wanted it to sum regardless of whether or not
it was greater than zero, so if I took out the (r12:r1008>0) I got a
value error. I fixed that by changing it to (r12:r1008<>0) and it
seems to work. Probably not entirely correct, but it did the trick.

Appreciate your help.

 

[T. Valko]

I wanted it to sum regardless of whether or not it

was greater than zero so if I took out the
(r12:r1008>0) I got a value error.

Hmmm...

This should work:

=SUMPRODUCT(--(MOD(ROW(R12:R1008)-ROW(R12),4)=0),R12:R1008)

 

[JimS]

Right you are. I tried that, but I left in the double negative before
the last r12:r1008. Replacing that with a comma did the trick.
Thanks again, Biff, much appreciated.

 

[T. Valko]

Good deal. Thanks for the feedback!