Help with formula

[Jennn]

Hi all. Any help is greatly appreciated on this:

I'm working with a database consisting of one and two digit numbers, if more than one number, it is separated by the || characters.

One column of the database looks something like this:

DATA
1
1||2
2
1||2||3||4||5||6
1||2||3||4||5||7
1||2||3||4||5||6||8
1||2||3||4||5||6||9

To do my analysis, I needed to count all the 1's in this column, all the 2's etc.etc.

I've been using this formula to do this:
=SUMPRODUCT((ISNUMBER(SEARCH("||"&1&"||","||"&A1:A9&"||"))+0))

My dilemma now is that I need to filter this by using a database formula of some sort and add criteria. If these were single numbers I'd use a formula such as =DCOUNT(A1:D9,1,G1:G2) where G1:G2 is my criteria range.

Does anyone have any suggestions for a database formula I can use that would do the same thing as my SUMPRODUCT formula above?

Again, any help is GREATLY appreciated. Thanks!

[Yogi Anand]

Hi Jennn:
How about using Autofilter with a custom criterion, to say apply a filter to select only those rows that contain 2; and then with the filtered data highlited go to StatusBar and look for the count of items filtered ... as an example
original data
DATA
1
1||2
2
1||2||3||4||5||6
1||2||3||4||5||7
1||2||3||4||5||6||8
1||2||3||4||5||6||9

now filtered data (with custom filter contains 2 applied)
DATA
1||2
1||2||3||4||5||6
1||2||3||4||5||7
1||2||3||4||5||6||8
1||2||3||4||5||6||9

and from the StatusBar I read the Count=6

HTH

Please post back if it works for you ... otherwise explain a litle further and let us take it from there!

[Yogi Anand]

Hi Jennn:
Subsequent to my posting I notice that the lone 2 in the extreme left position was not picked up ... I wonder why?

[Dawson]

Not quite sure I follow completely but won't this be much simpler if you convert your data using the "text to colums" feature and specify the delimiter character as ¦¦?

You should then be able to use the COUNTIF function.

[Yogi Anand]

Hi DAWSON:
By the way DATA|TEXT_to_COLUMN will not accept the || (double split vertical bar) as a delimiter ... but that's no problem, one can easily deal with that.
After that there is smooth sailing with COUNTIF()
Now JENNN knows whether doing this is feasible since it would involve creating several additional columns to accomodate the parsed data.