| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif range contains part of a value
I have a range of cells in column A. The values may range from:
10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
=COUNTIF(A:A,"*41*")
If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
antwaan wrote:
I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted once or twice? Or are you looking for only where "41" are the 4th and 5th characters in the cell? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Thanks Jacob, but this formula does not produce accurate results.
"Jacob Skaria" wrote: =COUNTIF(A:A,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
"41" will always be the 4th & 5th characters in the cell.
"Glenn" wrote: antwaan wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted once or twice? Or are you looking for only where "41" are the 4th and 5th characters in the cell? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Some of the cells may be in number format.
Suppose A is the column you have with this data.. in Col B enter the formula B1 = TEXT(A1,"?") and then try =COUNTIF(B:B,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: Thanks Jacob, but this formula does not produce accurate results. "Jacob Skaria" wrote: =COUNTIF(A:A,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Try this:
=SUMPRODUCT(--(MID(A1:A100,4,2)="41")) antwaan wrote: "41" will always be the 4th & 5th characters in the cell. "Glenn" wrote: antwaan wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted once or twice? Or are you looking for only where "41" are the 4th and 5th characters in the cell? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Oops
B1 = T(A1). Copy that down...which will convert the values to text and then apply CountIF If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: Thanks Jacob, but this formula does not produce accurate results. "Jacob Skaria" wrote: =COUNTIF(A:A,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
=TEXT(A1,"?") worked, =T(A1) didn't.
Thank you Jacob! "Jacob Skaria" wrote: Some of the cells may be in number format. Suppose A is the column you have with this data.. in Col B enter the formula B1 = TEXT(A1,"?") and then try =COUNTIF(B:B,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: Thanks Jacob, but this formula does not produce accurate results. "Jacob Skaria" wrote: =COUNTIF(A:A,"*41*") If this post helps click Yes --------------- Jacob Skaria "antwaan" wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Glenn - this worked great. Now I don't have to add in another column to
convert to text; plus it's more accurate. Thanks again. "Glenn" wrote: Try this: =SUMPRODUCT(--(MID(A1:A100,4,2)="41")) antwaan wrote: "41" will always be the 4th & 5th characters in the cell. "Glenn" wrote: antwaan wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted once or twice? Or are you looking for only where "41" are the 4th and 5th characters in the cell? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif range contains part of a value
Glad I could help.
Your responses here will almost always be more accurate when you provide more information. Your original request was open to many interpretations and possible solutions. antwaan wrote: Glenn - this worked great. Now I don't have to add in another column to convert to text; plus it's more accurate. Thanks again. "Glenn" wrote: Try this: =SUMPRODUCT(--(MID(A1:A100,4,2)="41")) antwaan wrote: "41" will always be the 4th & 5th characters in the cell. "Glenn" wrote: antwaan wrote: I have a range of cells in column A. The values may range from: 10041, 10041B to 10041PEM I need a formula to count the number of "41" that occurs. Please help! Any "41", like "10412" and "14102PEM"? What about "14141B"...is that counted once or twice? Or are you looking for only where "41" are the 4th and 5th characters in the cell? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find part data in row range? | Excel Worksheet Functions | |||
| Sumif with a range (part II) | Excel Worksheet Functions | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Painting Range - extend the range on the top part | Excel Discussion (Misc queries) | |||
| summing part of cells in a range | Excel Discussion (Misc queries) | |||
Linear Mode
