Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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). . . . |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple data ranges for countif | Excel Worksheet Functions | |||
Sum multiple arrays of data according to criteria | Excel Worksheet Functions | |||
Sumproduct assistance - need multiple ANDs and ORs | Excel Worksheet Functions | |||
LOOKUP across multiple arrays | Excel Discussion (Misc queries) | |||
sum of multiple arrays? | Excel Worksheet Functions |