Help with formula

Jennn

New Member
Joined
Mar 24, 2002
Messages
2
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!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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!
 
Upvote 0
Hi Jennn:
Subsequent to my posting I notice that the lone 2 in the extreme left position was not picked up ... I wonder why?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top