software

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,quot;1:5quot;) but that obviously didn't work. Any
advise?

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-Proquot;Lewis0205NCquot; wrote:

gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; advise?

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)))

quot;Lewis0205NCquot; wrote:

gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; advise?

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-Proquot;Lewis0205NCquot; wrote:

gt; It seems a little complicated but I tried it. I got an error. Please let me
gt; know what you think?
gt;
gt; =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0)))
gt;
gt; quot;Lewis0205NCquot; wrote:
gt;
gt; gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; gt; advise?

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.

quot;Ron Coderrequot; wrote:

gt; Well, now....It seems that your first example had the all of the data in the
gt; same column, but what you really wanted was an examination of data in a
gt; single row.
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0)))
gt;
gt; If you change the range, you'll probably need to tweak the MOD function.
gt; Currently, it calculates the remainder of each cell's column number divided
gt; by 3. If that remainder = 2, then the cell's value is tested, otherwise it's
gt; ignored.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Lewis0205NCquot; wrote:
gt;
gt; gt; It seems a little complicated but I tried it. I got an error. Please let me
gt; gt; know what you think?
gt; gt;
gt; gt; =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0)))
gt; gt;
gt; gt; quot;Lewis0205NCquot; wrote:
gt; gt;
gt; gt; gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; gt; gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; gt; gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; gt; gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; gt; gt; advise?

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
quot;remainderquot;. 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-Proquot;Lewis0205NCquot; wrote:

gt; Thank you very much. The formula did work but I was hoping to understand it
gt; a little better. I'd like to better understand how you formulated especially
gt; the first part of the formula so that for example, I could modify it if I
gt; wanted to examine data in the same row but maybe not every third column. Can
gt; you help or direct me to where I can get more information about forming these
gt; type of formulas. Thanks, again.
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Well, now....It seems that your first example had the all of the data in the
gt; gt; same column, but what you really wanted was an examination of data in a
gt; gt; single row.
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0)))
gt; gt;
gt; gt; If you change the range, you'll probably need to tweak the MOD function.
gt; gt; Currently, it calculates the remainder of each cell's column number divided
gt; gt; by 3. If that remainder = 2, then the cell's value is tested, otherwise it's
gt; gt; ignored.
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Lewis0205NCquot; wrote:
gt; gt;
gt; gt; gt; It seems a little complicated but I tried it. I got an error. Please let me
gt; gt; gt; know what you think?
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0)))
gt; gt; gt;
gt; gt; gt; quot;Lewis0205NCquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; gt; gt; gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; gt; gt; gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; gt; gt; gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; gt; gt; gt; advise?

Yes, it helps a lot and if I get in pinch again, I know I can post to this
board. Thanks, again.
Becky

quot;Ron Coderrequot; wrote:

gt; Here's the formula I posted:
gt;
gt; =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0)))
gt;
gt; Your interest is in its first functions
gt; 1)COLUMN()
gt; That function returns the column number of the reference.
gt; Col_A is 1, Col_B is 2, etc
gt;
gt; 2)MOD()
gt; The MOD function essentially peforms grade school division and returns the
gt; quot;remainderquot;. For example, if you divide 13 by 5, the grade school answer
gt; would be 2 with 3 left over. The MOD function version would be =MOD(13,5),
gt; which returns 3.
gt;
gt; By combining the MOD and COLUMN functions, we can isolate every third
gt; column, every 5th column, etc. In your specific case, you wanted every 3rd
gt; column, beginning with Col_H. Col_H is col number 8....MOD(8,3)=2...so, by
gt; applying that boolean (true/false) equation to every column in H through AH
gt; the column numbers with a remainder of 2 would return TRUE (the others would
gt; return FALSE). TRUE and FALSE, when used in a mathematical context, convert
gt; to 1 and 0, respectively.
gt;
gt; If you wanted to test values from every column, you'd just skip those first
gt; functions:
gt; =SUMPRODUCT(ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0)))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Lewis0205NCquot; wrote:
gt;
gt; gt; Thank you very much. The formula did work but I was hoping to understand it
gt; gt; a little better. I'd like to better understand how you formulated especially
gt; gt; the first part of the formula so that for example, I could modify it if I
gt; gt; wanted to examine data in the same row but maybe not every third column. Can
gt; gt; you help or direct me to where I can get more information about forming these
gt; gt; type of formulas. Thanks, again.
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Well, now....It seems that your first example had the all of the data in the
gt; gt; gt; same column, but what you really wanted was an examination of data in a
gt; gt; gt; single row.
gt; gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MAT CH(H8:AH8,{1,2,3,4,5},0)))
gt; gt; gt;
gt; gt; gt; If you change the range, you'll probably need to tweak the MOD function.
gt; gt; gt; Currently, it calculates the remainder of each cell's column number divided
gt; gt; gt; by 3. If that remainder = 2, then the cell's value is tested, otherwise it's
gt; gt; gt; ignored.
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Lewis0205NCquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; It seems a little complicated but I tried it. I got an error. Please let me
gt; gt; gt; gt; know what you think?
gt; gt; gt; gt;
gt; gt; gt; gt; =SUMPRODUCT((ROW(H8:AH8)={1,4,7,10,13,16,19,22,25} )*ISNUMBER(MATCH(H8:AH8,{1,2,3,4,5},0)))
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Lewis0205NCquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am trying to count the number of cells in a row that contain 1 to 5 but I
gt; gt; gt; gt; gt; cells are not adjacent. I am counting from every 3rd column cells which
gt; gt; gt; gt; gt; contain 1, 2, 3, 4, or 5. I tried using the
gt; gt; gt; gt; gt; =COUNTIF(A1,A4,A7,A10,A13,A16,quot;1:5quot;) but that obviously didn't work. Any
gt; gt; gt; gt; gt; advise?