How to Count the Number of Selected Records in a Dataset by OpenOffice CALC's DCOUNT Function

© 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:

A B C D E
1 Model Year MPG Purchasing Price Doors
2 Ford 2000 24 16000 4
3 Chevy 2008 22 17000 4
4 BMW 2008 17 29000 2
5 Mercedes 2002 25 47000 2
6 Dodge 2000 19 35000 4
7 Nissan 2001 30 18500 2
8 Hundai 2002 34 14750 2
9 Honda 2007 30 23500 4
10 Toyota 2002 22 27000 4
11
12
13 Model Year MPG Purchasing Price Doors
14
15
16

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:

OO CALC function can calculate the number of records in a dataset

OO CALC Example by Ugur Akinci


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.
OO Function Wizard comes in handy to select an OpenOffice function

OO Function Wizard comes in handy to select an OpenOffice function


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”.