Amateur here, asking "How do I change this to a database for

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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
On 2002-03-25 13:21, Jennn wrote:
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!

I don't think a d-function can do what the above SUMPRODUCT formula is doing. Is there any particular reason you want a d-function?

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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