| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count only Letters in cells that contain both letters and numbers | Excel Worksheet Functions | |||
| counting letters. | Excel Worksheet Functions | |||
| Counting Letters | Excel Worksheet Functions | |||
| Counting occurance of letters or numbers | Excel Discussion (Misc queries) | |||
| Counting groups of exact case numbers w/letters in them. | Excel Discussion (Misc queries) | |||
Linear Mode
