Need assistance with COUNTIF while using multiple data arrays
 

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

Need assistance with COUNTIF while using multiple data arrays
 

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

"Skyscan" wrote:

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

Need assistance with COUNTIF while using multiple data arrays
 

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


"Skyscan" wrote:

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

Need assistance with COUNTIF while using multiple data arrays
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Skyscan" wrote:
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

Need assistance with COUNTIF while using multiple data arrays
 

Nice one, Max

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

XL2003, WinXP


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Skyscan" wrote:
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

Need assistance with COUNTIF while using multiple data arrays
 

Thanks, Ron !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ron Coderre" wrote:
Nice one, Max

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

XL2003, WinXP

Need assistance with COUNTIF while using multiple data arrays
 

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

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

=COUNTIF(A2:C6,1)


--
Biff
Microsoft Excel MVP


"Tevuna" wrote in message
...
=COUNTIF((A2:C2):(A4:C4):(A6:C6),1)

"Skyscan" wrote:

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

Need assistance with COUNTIF while using multiple data arrays
 

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

"Tevuna" wrote:

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

"Skyscan" wrote:

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

Need assistance with COUNTIF while using multiple data arrays
 

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" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Skyscan" wrote:
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

Need assistance with COUNTIF while using multiple data arrays
 

welcome, Tom !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Skyscan" wrote in message
...
Thanks Max! I appreciate the help. This worked great! I have not been
able
to understand the code yet, but it worked. Thanks!

Tom

Need assistance with COUNTIF while using multiple data arrays
 

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

Skyscan wrote:
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). . . .

Need assistance with COUNTIF while using multiple data arrays
 

"Alan Beban" wrote...
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.

Need assistance with COUNTIF while using multiple data arrays
 

"Harlan Grove" wrote...
....
=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))

Need assistance with COUNTIF while using multiple data arrays
 

Harlan Grove wrote:
. . .
=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