Excel provides us with all kinds of calculations ; This is due to its many functions, some of which are better known than others. There are 2 useful functions for filtering the data in a table. That is why today you will see the differences between the DCOUNT and DCOUNT functions.

Excel has several functions that are related to databases. Such as the BDRODUCT and BDAVERAGE functions, or the BDMAX and BDMIN functions, to name a few. But the ones you’ll see today will help you with your data searches.

What is a database and what is it used for in Excel?

A database in Excel refers to a list or a group of data organized in a table , where each row represents a record. The first row of a table that will serve as a database will have the title or name of the field in each column.

All the rows below the titles are the data of each record . Depending on the title, we will place a numerical data, text or a formula that gives us a number or a text as a result.

For example: if we have a Sex field, we must put a representative text data such as: F and M or Female and Male; or if we have one that says Children, it is best to put the amount in numbers.

What are the data types in Excel?

Databases have various types of data. Excel only uses 3 types of data : numeric, text, and formulas; although among the numerical there are several types of format: Number, Currency, Accounting, Date, Time, Percentage, Fraction, and Scientific.

excel seleccion de formato de fecha

The important thing in any case is to always keep the same format . If for example we have a Date field, we cannot put in some records 12-15-2020, and in others 12-15-2020 or December 15, 2020.

What are the differences between the DCOUNT and DCOUNT functions?

Both functions are used in the same way, but their results will be different. BDCONTAR will only take into account numerical data and will ignore text data; while BDCONTARA will count only cells that have any type of text (even a number) and will discard those that are empty.

We must be aware of these differences, so that the review and filtering of the results is done correctly. The syntax of these functions is made up of 3 parts: Database, Field Name and Criteria.

The first part is the range of cells that the database will have to consult. We must select all the records in the table, including their titles or fields. For example, A2: J60 indicates that we select cells from column A to J, and rows from number 2 to 60.

In the second part, we have the column that we are going to analyze; you can enter the cell position or title between two double quotes. It is better to put the cell, to avoid mistakes when typing, or when changing the title; for example a field Children that later changed to Child (ren).

Finally, the third part is a range of cells composed only of fields or titles ; each of them will serve as a criteria or search condition. It is recommended to copy the titles from the database and paste them into other cells or on a different sheet.

excel ejemplo de funcion bdcontara

We apply the DCOUNT function like this: = DCOUNT (A3: J61; I3; L3: O4) . The range A3: J61 is the database, cell I3 is the Field to be analyzed (N ° Children), and the range L3: O4 are the search ones, such as: Sex (M / F), Marital Status , Salary and Profession.

The other function would be almost the same: = DCOUNTA (A3: J61; A3; L3: O4). But in the second part of the formula, a Field was used that does not have any records with empty cells ; the Name field is usually the one indicated for these cases.

Now you can analyze the Excel data . Depending on the case, even using variables with the BDVAR and BDVARP functions; in addition to improving its appearance by applying a custom style to the table. We hope this post has helped you and that you share it with your friends, to continue expanding our knowledge in Excel.

Helpful?, please rate!
[Total: 0 Average: 0]