Counting how many numbers and letters appear in a particular row
 

Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!

Counting how many numbers and letters appear in a particular row
 

Try
=SUM(COUNTIF(B9:AF9,{1,2,3,"V","H","CC"}))

If this post helps click Yes
---------------
Jacob Skaria


"dcb1" wrote:

Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!

Counting how many numbers and letters appear in a particular row
 

Please give me a couple of solutions using
the functions Count, CountIF and Sum.

How about a solution that uses the *best* method?

List your criteria in a range of cells.

A1:A6 = 1, 2, 3, V, H, CC

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A1:A6,0))))

--
Biff
Microsoft Excel MVP


"dcb1" wrote in message
...
Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!

Counting how many numbers and letters appear in a particular row
 

Thanks for your help!

On Aug 21, 12:41*am, Jacob Skaria
wrote:
Try
=SUM(COUNTIF(B9:AF9,{1,2,3,"V","H","CC"}))

If this post helps click Yes
---------------
Jacob Skaria



"dcb1" wrote:
Here is an example of what I am trying to do: *In row 9 col B to col
AF I have a drop down list. *A person can choose the number 1, 2, or
3, or choose the following letters: *V, H, F, T, S, CC, BR, A or leave
the cell blank. *Therefore, a person can select only one of those 12
choices for each cell in that row. *Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. * If there were 12
cells in that row, and each cell in order contained the following: *2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. * *My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. *Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!- Hide quoted text -

- Show quoted text -

Counting how many numbers and letters appear in a particular row
 

Thanks-- it worked. However, how do I get the range in your example
A1:A6 to stay constant when I copy to another row. Because when I
copied it, the range A1:A6 changes and therefore doesn't give me the
correct answer. I know I could copy for each row; however, I assume
there must be an easier way. Thanks again!

On Aug 21, 12:47*am, "T. Valko" wrote:
Please give me a couple of solutions using
the functions Count, CountIF and Sum.

How about a solution that uses the *best* method?

List your criteria in a range of cells.

A1:A6 = 1, 2, 3, V, H, CC

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A1:A6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...



Here is an example of what I am trying to do: *In row 9 col B to col
AF I have a drop down list. *A person can choose the number 1, 2, or
3, or choose the following letters: *V, H, F, T, S, CC, BR, A or leave
the cell blank. *Therefore, a person can select only one of those 12
choices for each cell in that row. *Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. * If there were 12
cells in that row, and each cell in order contained the following: *2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. * *My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. *Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!- Hide quoted text -

- Show quoted text -

Counting how many numbers and letters appear in a particular row
 

Make the reference to A1:A6 absolute:

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A$1:A$6,0))))

--
Biff
Microsoft Excel MVP


"dcb1" wrote in message
...
Thanks-- it worked. However, how do I get the range in your example
A1:A6 to stay constant when I copy to another row. Because when I
copied it, the range A1:A6 changes and therefore doesn't give me the
correct answer. I know I could copy for each row; however, I assume
there must be an easier way. Thanks again!

On Aug 21, 12:47 am, "T. Valko" wrote:
Please give me a couple of solutions using
the functions Count, CountIF and Sum.

How about a solution that uses the *best* method?

List your criteria in a range of cells.

A1:A6 = 1, 2, 3, V, H, CC

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A1:A6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...



Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!-
Hide quoted text -

- Show quoted text -

Counting how many numbers and letters appear in a particular row
 

It worked--- thank you!

On Aug 21, 1:29*am, "T. Valko" wrote:
Make the reference to A1:A6 absolute:

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A$1:A$6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...
Thanks-- it worked. *However, how do I get the range in your example
A1:A6 to stay constant when I copy to another row. *Because when I
copied it, the range A1:A6 changes and therefore doesn't give me the
correct answer. *I know I could copy for each row; however, I assume
there must be an easier way. *Thanks again!

*On Aug 21, 12:47 am, "T. Valko" wrote:



Please give me a couple of solutions using
the functions Count, CountIF and Sum.

How about a solution that uses the *best* method?

List your criteria in a range of cells.

A1:A6 = 1, 2, 3, V, H, CC

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A1:A6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...

Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum. Thanks!-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Counting how many numbers and letters appear in a particular row
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"dcb1" wrote in message
...
It worked--- thank you!

On Aug 21, 1:29 am, "T. Valko" wrote:
Make the reference to A1:A6 absolute:

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A$1:A$6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...
Thanks-- it worked. However, how do I get the range in your example
A1:A6 to stay constant when I copy to another row. Because when I
copied it, the range A1:A6 changes and therefore doesn't give me the
correct answer. I know I could copy for each row; however, I assume
there must be an easier way. Thanks again!

On Aug 21, 12:47 am, "T. Valko" wrote:



Please give me a couple of solutions using
the functions Count, CountIF and Sum.

How about a solution that uses the *best* method?

List your criteria in a range of cells.

A1:A6 = 1, 2, 3, V, H, CC

=SUMPRODUCT(--(ISNUMBER(MATCH(B9:AF9,A1:A6,0))))

--
Biff
Microsoft Excel MVP

"dcb1" wrote in message

...

Here is an example of what I am trying to do: In row 9 col B to col
AF I have a drop down list. A person can choose the number 1, 2, or
3, or choose the following letters: V, H, F, T, S, CC, BR, A or leave
the cell blank. Therefore, a person can select only one of those 12
choices for each cell in that row. Therefore, I want to count each
cell that contains either a 1, 2 , 3, V, H or CC. If there were 12
cells in that row, and each cell in order contained the following: 2,
V, S, F, S, 1, 3, Blank, T, H, F, Blank. My count would then be 5
because I only wanted to count the 2, V, 1, 3, H. Please give me a
couple of solutions using the functions Count, CountIF and Sum.
Thanks!-
Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -