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:

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