Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Logic Question 3
- Thread starter souprog
- Start date
-
3
- #2
DaleWatson123321
Programmer
souprog,
Especially because you are comparing Excel to Access, you should give some attention to Excel's "database" functions.
Indeed, Excel's "DCOUNT" database function will DEFINITELY work for you. The following is an example:
=DCOUNT(data,1,crit)
You'll notice it's made up of 3 arguments:
The 1st argument is a reference to the range where you have your data located. The range can be 1 - 256 columns and up to 65,535 rows.
The 2nd argument refers to the column in your database from which you want to COUNT the number of records that match your criteria. The first column is 1, the second is 2, etc.
The 3rd argument is a reference to the range where you have your "criteria" located.
In this example... =DCOUNT(data,1,crit) ...I have used "Range Names". Please NOTE: The use of Range Names is HIGHLY recommended for basically ALL uses of Excel. This is because "internally" Excel keeps track of the cell coordinates of Range Names, therefore making it much easier to insert rows and columns without concern for affecting, for example, VBA code which would otherwise have to be changed if the VBA was written with "hard-coded" cell coordinates.
Creating Range Names is real easy. Take these steps:
1) Highlight the range you wish to name
2) Use <Control> <F3> (Hold down <Control> and hit <F3>
3) Type the name
4) Hit <Enter>
An alternative to step #2, is to click on the "Name Box" which is the box immediately above the "A" of the 1st column.
A final point about Range Names: Do NOT use names which will conflict with Excel's cell coordinates or with numbers. For example, don't use a name like "A14" - instead use "A_14". Don't use a number like "23" - instead use "_23".
TESTING OUT THE FORMULA:
You might want to first test the DCOUNT formula on a separate worksheet, by taking the following steps:
1) In cell "A5", enter the text "colm1". This will be a "field name" for the first column of your database. In this case, we'll only use the one column, but the database could be set up with several fields.
2) With the cursor on "A5", hold down the "SHIFT" key and the "Page-Down" key until you've highlighted a sizeable number of rows.
3) Use <Control> <F3> (or click on the "Name Box"
4) Type the name "data", and hit <Enter>.
5) Copy the field name from cell "A5" to cell "D1" and cell "E1".
6) In cell "D2", type: >1
7) In cell "E2", type: <100
8) Place the cursor on cell "D1" and highlight the range "D1:E2".
9) Assign the Range Name "crit" to this range.
10) In cell "C1", enter the text "crit1". This will later serve as a reference for you - to know that the "criteria" range to the right is named "crit". With a large application, you might end up having SEVERAL separate "criteria", so it will be important to properly label each one.
11) You're now ready to enter the DCOUNT formula, and you could enter it anywhere - EXCEPT of course NOT within the range "data". For now, enter the formula into cell "A1", by simply typing: =DCOUNT(data,1,crit) and <Enter>.
Now test the formula by going to any cell within your defined database (the range named "data" - starting at cell "A6" - and enter numbers "within" the range of your criteria 2 - 99, and "outside" your criteria - <2 or >99.
And of course you'll notice that your formula in cell "A1" will ONLY count those numbers >1 and <100.
I hope you've found this a good "introduction" to the world of "Excel database formulas", and I trust it will resolve your immediate problem.
Please advise as to how you make out.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Especially because you are comparing Excel to Access, you should give some attention to Excel's "database" functions.
Indeed, Excel's "DCOUNT" database function will DEFINITELY work for you. The following is an example:
=DCOUNT(data,1,crit)
You'll notice it's made up of 3 arguments:
The 1st argument is a reference to the range where you have your data located. The range can be 1 - 256 columns and up to 65,535 rows.
The 2nd argument refers to the column in your database from which you want to COUNT the number of records that match your criteria. The first column is 1, the second is 2, etc.
The 3rd argument is a reference to the range where you have your "criteria" located.
In this example... =DCOUNT(data,1,crit) ...I have used "Range Names". Please NOTE: The use of Range Names is HIGHLY recommended for basically ALL uses of Excel. This is because "internally" Excel keeps track of the cell coordinates of Range Names, therefore making it much easier to insert rows and columns without concern for affecting, for example, VBA code which would otherwise have to be changed if the VBA was written with "hard-coded" cell coordinates.
Creating Range Names is real easy. Take these steps:
1) Highlight the range you wish to name
2) Use <Control> <F3> (Hold down <Control> and hit <F3>
3) Type the name
4) Hit <Enter>
An alternative to step #2, is to click on the "Name Box" which is the box immediately above the "A" of the 1st column.
A final point about Range Names: Do NOT use names which will conflict with Excel's cell coordinates or with numbers. For example, don't use a name like "A14" - instead use "A_14". Don't use a number like "23" - instead use "_23".
TESTING OUT THE FORMULA:
You might want to first test the DCOUNT formula on a separate worksheet, by taking the following steps:
1) In cell "A5", enter the text "colm1". This will be a "field name" for the first column of your database. In this case, we'll only use the one column, but the database could be set up with several fields.
2) With the cursor on "A5", hold down the "SHIFT" key and the "Page-Down" key until you've highlighted a sizeable number of rows.
3) Use <Control> <F3> (or click on the "Name Box"
4) Type the name "data", and hit <Enter>.
5) Copy the field name from cell "A5" to cell "D1" and cell "E1".
6) In cell "D2", type: >1
7) In cell "E2", type: <100
8) Place the cursor on cell "D1" and highlight the range "D1:E2".
9) Assign the Range Name "crit" to this range.
10) In cell "C1", enter the text "crit1". This will later serve as a reference for you - to know that the "criteria" range to the right is named "crit". With a large application, you might end up having SEVERAL separate "criteria", so it will be important to properly label each one.
11) You're now ready to enter the DCOUNT formula, and you could enter it anywhere - EXCEPT of course NOT within the range "data". For now, enter the formula into cell "A1", by simply typing: =DCOUNT(data,1,crit) and <Enter>.
Now test the formula by going to any cell within your defined database (the range named "data" - starting at cell "A6"
- and enter numbers "within" the range of your criteria 2 - 99, and "outside" your criteria - <2 or >99.And of course you'll notice that your formula in cell "A1" will ONLY count those numbers >1 and <100.
I hope you've found this a good "introduction" to the world of "Excel database formulas", and I trust it will resolve your immediate problem.
Please advise as to how you make out.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Thread starter
- #3
Dale,
I've given then named range of criteria a go, but it still doesn;t seem to work. Through some further testing it works if i only specify one criteria - as soon as i add the second criteria (the top limit) the answer returned is zero.
Thanks for your help.
souprog
I've given then named range of criteria a go, but it still doesn;t seem to work. Through some further testing it works if i only specify one criteria - as soon as i add the second criteria (the top limit) the answer returned is zero.
Thanks for your help.
souprog
DaleWatson123321
Programmer
souprog,
I just realized you replied right away. If you are still there, why not email me - at my HOME address, and I'll email you the file I created specifically for your situation. It DEFINITELY works.
Regards, ...Dale Watson HOME: nd.watson@shaw.ca
I just realized you replied right away. If you are still there, why not email me - at my HOME address, and I'll email you the file I created specifically for your situation. It DEFINITELY works.
Regards, ...Dale Watson HOME: nd.watson@shaw.ca
DaleWatson123321
Programmer
souprog,
It seems I missed you.
Email me at work tomorrow morning, and I'll send the file via return email.
Once you see the example file, you'll see how "easy" it really is. And I can send other example database files later if you wish.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
It seems I missed you.
Email me at work tomorrow morning, and I'll send the file via return email.
Once you see the example file, you'll see how "easy" it really is. And I can send other example database files later if you wish.
Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca
DaleWatson123321
Programmer
souprog,
I'm curious to know how you have made out !!!
Please provide an update.
Thanks.
Regards, ...Dale Watson
I'm curious to know how you have made out !!!
Please provide an update.
Thanks.
Regards, ...Dale Watson
SkipVought
Programmer
souprog,
I hope that you will recognize Dale for his fine and detailed response to your questions as both helpful and expert. I certainly found them to be so.
Consequently...
LOL Skip,
metzgsk@voughtaircraft.com
I hope that you will recognize Dale for his fine and detailed response to your questions as both helpful and expert. I certainly found them to be so.
Consequently...
Code:
With Contributor
.Post = vbHelpfulExpert (:I
.Recognition = vbSTAR *:->*
End Withmetzgsk@voughtaircraft.com
DaleWatson123321
Programmer
Skip,
VERY "cute" !!!
...THANKS for confirming that it works - and "meets with your (esteemed) approval" ...and THANKS for the STAR.
...Dale
VERY "cute" !!!
...THANKS for confirming that it works - and "meets with your (esteemed) approval" ...and THANKS for the STAR.
...Dale
- Thread starter
- #9
Dale
I got it to work!! (I wasn't including the column names in the data ranges).
I have also come accross another solution for my original problem:
=sum((data>1)*(data<=100)) Control-Shift-Enter (to make it an array)
seems to also work...
Thanks for your help
Souprog
I got it to work!! (I wasn't including the column names in the data ranges).
I have also come accross another solution for my original problem:
=sum((data>1)*(data<=100)) Control-Shift-Enter (to make it an array)
seems to also work...
Thanks for your help
Souprog
- Status