Need assistance with COUNTIF while using multiple data arrays

[Skyscan]

I have a data array consisting of 6 rows of numbers. Each row has a number
in each of the first 3 columns.

8 4 3
4 1 9
2 6 4
9 5 7
3 4 8
9 1 7

My objective is to count the number of times that the number "1" occurs in
the even rows (rows 2, 4 & 6). I was intending to use the COUNTIF function
for the 3 targeted rows, however I am not aware of how to do this. I tried
this approach...

=COUNTIF(A2:C2, A4:C4, A6:C6, "1")

....however it created a syntax error.

I realize that I could sum 3 separate COUNTIF function calls - one for each
row, however I was trying to use a single COUNTIF function call.

Please assist.

Thanks in advance!!

Tom

 

[Tevuna]

=COUNTIF((A2:C2)A4:C4)A6:C6),1)

 

[Ron Coderre]

Try something like this:
=SUMPRODUCT(--ISNUMBER(1/(MOD(ROW(A1:A10),2)=0)*SEARCH("_1_",("_"&A1:A10&"_"&B1:B10&"_"&C1:C10&"_"))))

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Max]

Another way ..
Try in say, F2:
=SUMPRODUCT((A1:C6=E2)*(MOD(ROW(A1:A6),2)=0))
where E2 will house the desired number, eg: 1

 

[Ron Coderre]

Nice one, Max

(Makes my mess look even messier! I don't know WHAT I was thinking.)
***********
Regards,
Ron

XL2003, WinXP

 

[Max]

Thanks, Ron !

 

[T. Valko]

=COUNTIF((A2:C2)A4:C4)A6:C6),1)

That's not doing what you think. This is how that evaluates:

=COUNTIF(A2:C6,1)

 

[Skyscan]

Thanks so much for the assistance. I tried this and it seems to not overlook
the data in the odd rows. Did I do something wrong?

Regards,

Tom

 

[Skyscan]

Thanks Max! I appreciate the help. This worked great! I have not been able
to understand the code yet, but it worked. Thanks!

Tom

 

[Max]

welcome, Tom !

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCountIf(ArrayAlternates(A1:C6,False),4)

will return the number of 4s in the even numbered rows (i.e., 1 for the
sample data posted).

Alan Beban

 

[Harlan Grove]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayCountIf(ArrayAlternates(A1:C6,False),4)

will return the number of 4s in the even numbered rows (i.e., 1 for the
sample data posted).

....

But why use udfs which are always slower than several times the number of
built-in function calls? Hardcoding the modulus sought,

=SUMPRODUCT((MOD(ROW(A1:C6),2)=0)*(A1:C6=1))

returns the number of 1s (the number the OP mentioned, just for kicks) in
the odd numbered rows. And it's even a shorter formula. And it can be easily
adapted to do this for every Nth row,

=SUMPRODUCT((MOD(ROW(A1:C6),N)=0)*(A1:C6=1))

which ArrayAlternates doesn't do.

 

[Harlan Grove]

=SUMPRODUCT((MOD(ROW(A1:C6),2)=0)*(A1:C6=1))

returns the number of 1s (the number the OP mentioned, just for kicks) in
the odd numbered rows. . . .

Oops! In the even numbered rows. For odd numbered rows,

=SUMPRODUCT((MOD(ROW(A1:C6),2)=1)*(A1:C6=1))

 

[Alan Beban]

. . .
=SUMPRODUCT((MOD(ROW(A1:C6),2)=0)*(A1:C6=1))

returns the number of 1s (the number the OP mentioned, just for kicks) in
the odd numbered rows. . . .

Just to avoid confusion, it's the even numbered rows. Not sure from the
above just what the analogous formula would be if it were the odd rows
to be tested.

I used 4s instead of 1s because the OP already got a "solution" that
correctly returned the number of 1s in the even numbered rows, but only
because that was the same as the number of 1s in ALL rows. The incidence
of 4s didn't have that problem.

Alan Beban