Latest posts by techwriter (see all)
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
- 3 Ways to Add Copyright Free Images to Your Blogs, Books and Documents - September 19, 2016
- How to Delete All Hyperlinks in a MS #Word Document through VBA Macro - September 1, 2016
© 2010 Ugur Akinci
OpenOffice is a free office applications suite provided by Sun Microsystems. (http://www.openoffice.org/)
OpenOffice CALC is a powerful spreadsheet program which corresponds to Microsoft Excel.
Here is how you can filter and count the number of records (rows) in a dataset by using OpenOffice CALC:
Consider the following imaginary data set for a collection of automobiles:
Here is the general syntax for OpenOffice DCOUNT function:
DCOUNT(Database; DatabaseField; SearchCriteria)
DCOUNT counts NUMERIC values only.
OpenOffice searches a database according to the criteria you specify. Selects the data that fits the criteria and then counts the records (rows) that satisfy that criteria.
For example, here is how you can calculate the number of cars (records) that has miles-per-gallon rating of over 20 mpg and display the result in cell A15:
Click and select cell A15 and then enter the formula =DCOUNT(A1:E10;0;A13:E14) into the formula bar.
“0” means CALC will look at all fields (columns). I recommend entering “0” to make things simpler.
A13:E14 is the selected field where you will enter selection (filtering) criteria.
Enter “>20” to cell C14 as your selection criterion.
Or click the FUNCTION button to display the FUNCTION WIZARD which can enter the formula automatically for you when you make the right selections for the three fields (Database, Database Field, Search Criteria). Click OK when done.
The result (7) will be displayed in cell A15.
When you change the selection criteria entered into the field A13:E14, the result displayed in A15 also changes automatically.
For example what if you want the number of records (cars) that has more than 20 mpg but were made in year 2002?
You enter “2002” into the cell 2002 and click the green CHECK MARK next to the formula bar. The result in A15 is automatically recalculated and displayed as “3”.
Perhaps the power of this function is not clear enough when you have 10 or 20 records. But imagine having a dataset of 50,000 records. Wouldn’t be great to instantly know the number of cars that go over 20 mpg and were manufactured in (or before, after) 2002? For cars built after 2002, you would use the expression “>2002”. For those made earlier, you’d use “<2002”.