Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numbers?
I am trying to count the number of cells in a row that contain 1 to 5 but I
cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numbers?
For a relatively small range like you are using, try this:
B1: =SUMPRODUCT((ROW(A1:A16)={1,4,7,10,13,16})*ISNUMBE R(MATCH(A1:A16,{1,2,3,4,5},0))) or maybe this: B1: =SUMPRODUCT((MOD(ROW(A1:A16),3)=1)*ISNUMBER(MATCH( A1:A16,{1,2,3,4,5},0))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numbers?
It seems a little complicated but I tried it. I got an error. Please let me
know what you think? =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numb
Well, now....It seems that your first example had the all of the data in the
same column, but what you really wanted was an examination of data in a single row. Try this: =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) If you change the range, you'll probably need to tweak the MOD function. Currently, it calculates the remainder of each cell's column number divided by 3. If that remainder = 2, then the cell's value is tested, otherwise it's ignored. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: It seems a little complicated but I tried it. I got an error. Please let me know what you think? =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numb
Thank you very much. The formula did work but I was hoping to understand it
a little better. I'd like to better understand how you formulated especially the first part of the formula so that for example, I could modify it if I wanted to examine data in the same row but maybe not every third column. Can you help or direct me to where I can get more information about forming these type of formulas. Thanks, again. "Ron Coderre" wrote: Well, now....It seems that your first example had the all of the data in the same column, but what you really wanted was an examination of data in a single row. Try this: =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) If you change the range, you'll probably need to tweak the MOD function. Currently, it calculates the remainder of each cell's column number divided by 3. If that remainder = 2, then the cell's value is tested, otherwise it's ignored. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: It seems a little complicated but I tried it. I got an error. Please let me know what you think? =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numb
Here's the formula I posted:
=SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) Your interest is in its first functions 1)COLUMN() That function returns the column number of the reference. Col_A is 1, Col_B is 2, etc 2)MOD() The MOD function essentially peforms grade school division and returns the "remainder". For example, if you divide 13 by 5, the grade school answer would be 2 with 3 left over. The MOD function version would be =MOD(13,5), which returns 3. By combining the MOD and COLUMN functions, we can isolate every third column, every 5th column, etc. In your specific case, you wanted every 3rd column, beginning with Col_H. Col_H is col number 8....MOD(8,3)=2...so, by applying that boolean (true/false) equation to every column in H through AH the column numbers with a remainder of 2 would return TRUE (the others would return FALSE). TRUE and FALSE, when used in a mathematical context, convert to 1 and 0, respectively. If you wanted to test values from every column, you'd just skip those first functions: =SUMPRODUCT(ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: Thank you very much. The formula did work but I was hoping to understand it a little better. I'd like to better understand how you formulated especially the first part of the formula so that for example, I could modify it if I wanted to examine data in the same row but maybe not every third column. Can you help or direct me to where I can get more information about forming these type of formulas. Thanks, again. "Ron Coderre" wrote: Well, now....It seems that your first example had the all of the data in the same column, but what you really wanted was an examination of data in a single row. Try this: =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) If you change the range, you'll probably need to tweak the MOD function. Currently, it calculates the remainder of each cell's column number divided by 3. If that remainder = 2, then the cell's value is tested, otherwise it's ignored. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: It seems a little complicated but I tried it. I got an error. Please let me know what you think? =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I count cells that contain one of several specific numb
Yes, it helps a lot and if I get in pinch again, I know I can post to this
board. Thanks, again. Becky "Ron Coderre" wrote: Here's the formula I posted: =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) Your interest is in its first functions 1)COLUMN() That function returns the column number of the reference. Col_A is 1, Col_B is 2, etc 2)MOD() The MOD function essentially peforms grade school division and returns the "remainder". For example, if you divide 13 by 5, the grade school answer would be 2 with 3 left over. The MOD function version would be =MOD(13,5), which returns 3. By combining the MOD and COLUMN functions, we can isolate every third column, every 5th column, etc. In your specific case, you wanted every 3rd column, beginning with Col_H. Col_H is col number 8....MOD(8,3)=2...so, by applying that boolean (true/false) equation to every column in H through AH the column numbers with a remainder of 2 would return TRUE (the others would return FALSE). TRUE and FALSE, when used in a mathematical context, convert to 1 and 0, respectively. If you wanted to test values from every column, you'd just skip those first functions: =SUMPRODUCT(ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: Thank you very much. The formula did work but I was hoping to understand it a little better. I'd like to better understand how you formulated especially the first part of the formula so that for example, I could modify it if I wanted to examine data in the same row but maybe not every third column. Can you help or direct me to where I can get more information about forming these type of formulas. Thanks, again. "Ron Coderre" wrote: Well, now....It seems that your first example had the all of the data in the same column, but what you really wanted was an examination of data in a single row. Try this: =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0))) If you change the range, you'll probably need to tweak the MOD function. Currently, it calculates the remainder of each cell's column number divided by 3. If that remainder = 2, then the cell's value is tested, otherwise it's ignored. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Lewis0205NC" wrote: It seems a little complicated but I tried it. I got an error. Please let me know what you think? =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0))) "Lewis0205NC" wrote: I am trying to count the number of cells in a row that contain 1 to 5 but I cells are not adjacent. I am counting from every 3rd column cells which contain 1, 2, 3, 4, or 5. I tried using the =COUNTIF(A1,A4,A7,A10,A13,A16,"1:5") but that obviously didn't work. Any advise? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add in numbers automatically based on adjacent cells cont | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
How can I count cells of a specific colour (pattern)? | Excel Worksheet Functions | |||
Need formula to count specific cells in a row | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions |