| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF(RANGE,"?") and search for contains
How do I perform a countif with the criteria being I want to count the number
of question marks? Also, can I do countifs in a if it contains something versus equals something? Say a cell contains "Guadalajara, Mexico" can I get somrhting like =countif(cell,contains"Mexico") to return a 1? Thank you! (Excel 2003 on XP Pro. Also curious how to do it in Excel 2007 on XP Pro) Thanks -- BlueWolverine MSE - Mech. Eng. Go BLUE! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
try this
=COUNTIF(A1:A30,"*Mexico*") and =COUNTIF(A1:A30,"?") Mike "BlueWolverine" wrote: How do I perform a countif with the criteria being I want to count the number of question marks? Also, can I do countifs in a if it contains something versus equals something? Say a cell contains "Guadalajara, Mexico" can I get somrhting like =countif(cell,contains"Mexico") to return a 1? Thank you! (Excel 2003 on XP Pro. Also curious how to do it in Excel 2007 on XP Pro) Thanks -- BlueWolverine MSE - Mech. Eng. Go BLUE! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
One way:
To count single question marks =COUNTIF(A:A,"~?") To count the number of cells in a range that contain a question mark anywhere within their content: =COUNTIF(A:A,"*~?*") Similarly, =COUNTIF(A1,"*Mexico*") In article , BlueWolverine wrote: How do I perform a countif with the criteria being I want to count the number of question marks? Also, can I do countifs in a if it contains something versus equals something? Say a cell contains "Guadalajara, Mexico" can I get somrhting like =countif(cell,contains"Mexico") to return a 1? Thank you! (Excel 2003 on XP Pro. Also curious how to do it in Excel 2007 on XP Pro) Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
Your last example will count any cell that contains a single character -
the question mark acts as a wild card. To look for the question mark itself, you need to escape it with the tilde character: =COUNTIF(A1:A30,"~?") In article , Mike H wrote: try this =COUNTIF(A1:A30,"*Mexico*") and =COUNTIF(A1:A30,"?") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
Of course it does, thanks for the correction
Mike "JE McGimpsey" wrote: Your last example will count any cell that contains a single character - the question mark acts as a wild card. To look for the question mark itself, you need to escape it with the tilde character: =COUNTIF(A1:A30,"~?") In article , Mike H wrote: try this =COUNTIF(A1:A30,"*Mexico*") and =COUNTIF(A1:A30,"?") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
Thank you. That locks it up.
-- BlueWolverine MSE - Mech. Eng. Go BLUE! "JE McGimpsey" wrote: One way: To count single question marks =COUNTIF(A:A,"~?") To count the number of cells in a range that contain a question mark anywhere within their content: =COUNTIF(A:A,"*~?*") Similarly, =COUNTIF(A1,"*Mexico*") In article , BlueWolverine wrote: How do I perform a countif with the criteria being I want to count the number of question marks? Also, can I do countifs in a if it contains something versus equals something? Say a cell contains "Guadalajara, Mexico" can I get somrhting like =countif(cell,contains"Mexico") to return a 1? Thank you! (Excel 2003 on XP Pro. Also curious how to do it in Excel 2007 on XP Pro) Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF(RANGE,"?") and search for contains
Let me clarify, that fixes my problems. I just reread my post that makes it
sound like I am being a smart @$$ and telling you your fix locks my computer up. ALL GOOD! -- BlueWolverine MSE - Mech. Eng. Go BLUE! "BlueWolverine" wrote: Thank you. That locks it up. -- BlueWolverine MSE - Mech. Eng. Go BLUE! "JE McGimpsey" wrote: One way: To count single question marks =COUNTIF(A:A,"~?") To count the number of cells in a range that contain a question mark anywhere within their content: =COUNTIF(A:A,"*~?*") Similarly, =COUNTIF(A1,"*Mexico*") In article , BlueWolverine wrote: How do I perform a countif with the criteria being I want to count the number of question marks? Also, can I do countifs in a if it contains something versus equals something? Say a cell contains "Guadalajara, Mexico" can I get somrhting like =countif(cell,contains"Mexico") to return a 1? Thank you! (Excel 2003 on XP Pro. Also curious how to do it in Excel 2007 on XP Pro) Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF "EVEN" numbers in a range | Excel Discussion (Misc queries) | |||
| =IF(ISERROR(SEARCH("insurance",A125,1)),"","*") | Excel Worksheet Functions | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
