Formatting of ISNUMBER Results

[Janet7]

Hi

I used the ISNUMBER function to produce me a result of "1" or "0".

I wanted to count how many of my results contained the letters 'CBC' so the the formula I used was =IF(ISNUMBER(SEARCH("CBC",AF2)),"1","0")

I want to add up my results however the results do not seem to be formatted as numbers and wont add into my subtotal.

I have tried all different formatting options but it still seems to be treated as text rather than numbers. I have tried removing the "" from the result but the formula fails.

Please help !

 

[p45cal]

1.
Change
=IF(ISNUMBER(SEARCH("CBC",AF2)),"1","0")
to
=IF(ISNUMBER(SEARCH("CBC",AF2)),1,0)

2. Make sure the cells are not now formatted as Text, as they may be, as a result of the previous formulae. Use General.

 

[T. Valko]

Hi

I used the ISNUMBER function to produce me a result of "1" or "0".

I wanted to count how many of my results contained the letters 'CBC' so the the formula I used was =IF(ISNUMBER(SEARCH("CBC",AF2)),"1","0")

I want to add up my results however the results do not seem to be formatted as numbers and wont add into my subtotal.

I have tried all different formatting options but it still seems to be treated as text rather than numbers. I have tried removing the "" from the result but the formula fails.

Please help !

Remove the quotes from around the numbers:

=IF(ISNUMBER(SEARCH("CBC",AF2)),1,0)

If you want a result of 1 or 0 then we can reduce that to:

=COUNT(SEARCH("CBC",AF2))

 

[Janet7]

Thankyou both sp much - I have changed the formatting and removed the quotes and my subtotals now work.

I had been trying to fix this for hours !

 

[T. Valko]

Thankyou both sp much - I have changed the formatting and removed the quotes and my subtotals now work.

I had been trying to fix this for hours !

See how destiny works?

I've been sitting here for hours just waiting for a good question to answer!

Thanks for the feedback!

 

[MrKowz]

Welcome to the forums!

If you are wanting to get a count of how many cells in a range contain "cbc", you can use the COUNTIF function with wildcards:


Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>this cbc that</TD><TD>Count:</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>cbc0099</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Whereiscbc</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Ahh, we meet again, cbc</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>None here</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>here either</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>potato</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>H1</TH><TD style="TEXT-ALIGN: left">=COUNTIF(F:F,"*cbc*")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>