COUNTIF question

[MaDroskilo]

Hello everyone,

What I am trying to do is in a single column, count all the non-zero elements that appear in every 5th row. I tried to use the COUNTIFS function. However, the second criterion (selecting every 5th row) does not seem to work. Even when I use COUNTIF() (see below) with this single criterion, 0 is always returned.

As an example, if I would use this function --> COUNTIF(A50:A59,MOD(ROW(A50:A59),5)=0) I would expect 2 as an output. What am I missing? Or is there a more elaborate way to handle this?

Thank you in advance!

 

[jtakw]

Hi,

COUNTIF and COUNTIFS can't handle arrays like that, you'll need to use SUMPRODUCT:

Book1
	A	B	C
50	1		2
51	0
52	3
53	4
54	5
55	6
56	0
57	7
58	8
59	9
Sheet396

Cell Formulas
Range	Formula
C50	=SUMPRODUCT((A50:A59<>0)*(MOD(ROW(A50:A59),5)=0))

 

[Aladin Akyurek]

Try....

=SUMPRODUCT(--(MOD(ROW(A50:A59)-ROW(A50),5)=0),--(A50:A59>0))