How do I count cells that contain one of several specific numbers?

[Guest]

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?

 

[Guest]

For a relatively small range like you are using, try this:

B1:
=SUMPRODUCT((ROW(A1:A16)={1,4,7,10,13,16})*ISNUMBER(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

 

[Guest]

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

 

[Guest]

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(MATCH(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

 

[Guest]

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.

 

[Guest]

Here's the formula I posted:

=SUMPRODUCT((MOD(COLUMN(H8:AH8),3)=2)*ISNUMBER(MATCH(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

 

[Guest]

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