Countif Question

  • Thread starter Thread starter JimS
  • Start date Start date
J

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
 
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.
 
How would I modify the second formula if I wanted to sum the amounts
in every fourth cell in stead of counting them?
 
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)
 
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.
 
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.
Click to expand...

Hmmm...

This should work:

=SUMPRODUCT(--(MOD(ROW(R12:R1008)-ROW(R12),4)=0),R12:R1008)
 
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.
 
Back
Top