Posted by Super-T on December 10, 2001 8:22 PM

countif will not work.

Are all the numbers single digits?

Posted by Super-T on December 10, 2001 8:24 PM

Countif will not work.

Question - are all the numbers single digits?

Posted by Dan on December 10, 2001 8:33 PM

Noo, unfortunately there are 1 and 2 digit numbers :-(

Posted by Juan Pablo G. on December 10, 2001 8:44 PM

How about

=(LEN(A1) - LEN(SUBSTITUTE(A1,"||","")))/2 + 1

Juan Pablo G.

Posted by Dan on December 10, 2001 9:21 PM

Re: Can the - Getting close, but....

Juan, that formula works great for counting all the numbers in one cell. Can it be altered to count a specific number in a range of cells?

For example if I had the following one column of numbers:

1||2||3

2||3||4

3||4||5

1||2||3

and I wanted to count all the 3's in this one column, which formula could I use to do this? The outcome would be 4.

Thanks so much for the input! :)

Posted by Mike C on December 11, 2001 4:10 AM

use the following formula entered with Ctrl-Shift-Enter (for an array formula)

=COUNT(IF(NOT(ISERROR(FIND("||8||","||"&A1:A4&"||"))),1))

Where A1:A4 is the cell range you want to search and "||8||" contains the number you want to find (8 in this example)

This works for n-digit numbers.

Posted by Juan Pablo G. on December 11, 2001 5:38 AM

Re: Can the - Getting close, but....

If your numbers above are in A1:A4, put the number you want to search in B1 and this formula somewhere else.

=SUMPRODUCT(ISNUMBER(FIND(B1,$A$1:$A$4))+0)

Juan Pablo G.

Posted by Aladin Akyurek on December 11, 2001 7:39 AM

Mike --

The most important aspect of your formula is that it adds a welcome regularity to Dan's data. That of adding a "||" at the beginning and at the end of the search string and the target string. That's smart. That introduces the difference between a 1 and 11, 21, etc.

I'd like however to suggest the following array formula as alternative:

{=SUM((ISNUMBER(FIND("||"&1&"||","||"&A1:A7&"||"))+0))}

and its non-CSE sister:

=SUMPRODUCT((ISNUMBER(SEARCH("||"&1&"||","||"&A1:A7&"||"))+0))

The 1 as the first arg can be replaced by a cell ref that houses this number. Note that SEARCH or FIND both can be used in the context of Dan's problem.

Dan --

I had the impression that you used the whole column A as the target range. The array (or CSE) and SUMPRODUCT formulas, it doesn't matter, whichever you use will be too costly in performance! So be warned. Moreover, these 2 types of formulas don't accept A:A as a range arg. But, there is UDF (by Harlan Grove) which can be used to achieve just that.

Aladin

=========

Posted by Dan on December 11, 2001 9:30 AM

Re: Can the - Getting close, but....THANK YOU!

You're a lifesaver Juan, it works perfectly!!

Posted by Dan on December 11, 2001 9:35 AM

Thank you Mike and Aladin. Your input is extremely helpful, you have no idea how much this saves me!!