| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif based of partial cell contents
II need help modifying a formula.
=COUNTIF($D$6:$D$1006,H1) This returns the count for the value in H1 fine, but I need to modify it so it will still return the correct number if there are spaces or characters before or after the value in H1. Example: If H1 contains "BBDC3245---" or a space exists before or after " BBDC3245 " ,I want to still count all the occurrences of that base number in col D6to D1006. I guess I need kind of a wild card search? -- Tony S. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif based of partial cell contents
Try this:
H1 = BBDC3245 (no spaces) =COUNTIF(D6:D1006,"*"&H1&"*") -- Biff Microsoft Excel MVP "Tony S." wrote in message ... II need help modifying a formula. =COUNTIF($D$6:$D$1006,H1) This returns the count for the value in H1 fine, but I need to modify it so it will still return the correct number if there are spaces or characters before or after the value in H1. Example: If H1 contains "BBDC3245---" or a space exists before or after " BBDC3245 " ,I want to still count all the occurrences of that base number in col D6to D1006. I guess I need kind of a wild card search? -- Tony S. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif based of partial cell contents
That works great for no spaces. Thanks for the help.
-- Tony S. "T. Valko" wrote: Try this: H1 = BBDC3245 (no spaces) =COUNTIF(D6:D1006,"*"&H1&"*") -- Biff Microsoft Excel MVP "Tony S." wrote in message ... II need help modifying a formula. =COUNTIF($D$6:$D$1006,H1) This returns the count for the value in H1 fine, but I need to modify it so it will still return the correct number if there are spaces or characters before or after the value in H1. Example: If H1 contains "BBDC3245---" or a space exists before or after " BBDC3245 " ,I want to still count all the occurrences of that base number in col D6to D1006. I guess I need kind of a wild card search? -- Tony S. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif based of partial cell contents
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Tony S." wrote in message ... That works great for no spaces. Thanks for the help. -- Tony S. "T. Valko" wrote: Try this: H1 = BBDC3245 (no spaces) =COUNTIF(D6:D1006,"*"&H1&"*") -- Biff Microsoft Excel MVP "Tony S." wrote in message ... II need help modifying a formula. =COUNTIF($D$6:$D$1006,H1) This returns the count for the value in H1 fine, but I need to modify it so it will still return the correct number if there are spaces or characters before or after the value in H1. Example: If H1 contains "BBDC3245---" or a space exists before or after " BBDC3245 " ,I want to still count all the occurrences of that base number in col D6to D1006. I guess I need kind of a wild card search? -- Tony S. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Parse cell value based on contents | Excel Discussion (Misc queries) | |||
| sum based on PARTIAL content of another cell | Excel Worksheet Functions | |||
| sum if based on PARTIAL content of another cell | Excel Worksheet Functions | |||
| Sorting on partial Cell contents VBA | Excel Discussion (Misc queries) | |||
| COUNTIF for a partial value | Excel Worksheet Functions | |||
![Tony S.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
