Syntax. DCOUNTA(database,field,criteria)

Definition. This function counts the number of cells in a column, list, or database that are not empty and match the specified conditions.

Arguments

Background. DCOUNTA() counts nonempty cells, in contrast to DCOUNT(), which counts number values.

Example. Because your business is relatively new, you want to know how many invoices were sent to companies within the United States. You also want to know the total number of invoices in the database so that you can calculate the percentage of the invoices for companies within the United States.

You open a new worksheet and define the criteria range (the Country/Region field from the original data). Then you specify the search criterion USA (see Figure 13-9).

Figure 13-9. Calculating the number of records containing USA as the country/region.

Because the DCOUNTA() function counts text, it returns the number of records matching the criterion USA.

Specify the cell range containing the database for the database argument, such as A1:F7008. In this example, that cell range has the dynamic name Database.

You get the same result if you enter the name SEARCH2 for the criteria range (A4:A5 in this example) and enter “country/region” instead of cell A4 in the field argument. Remember to enclose the field name, country/region, in quotation marks.

The result is still 1001; the database contains 1,001 records matching the criterion USA: 1,001 invoices were sent to companies in the United States (see Figure 13-10).

Figure 13-10. Different arguments for the DCOUNTA() function.

Note

The calculation of the invoices in the United States does not consider the sales values. Although it is very unlikely that invoices were issued for orders without sales, you can take this into account by adding the sales field to the criteria range and specifying the conditions and formula shown in Figure 13-11.

Figure 13-11. Although there are probably no invoices issued for sales with the value 0, you can add a second filter criterion to ensure that you get the correct number of invoices.

Now you calculate the total number of issued invoices in the same way. The search range doesn’t change. Refer the search criterion to an empty cell to count all records in the search range country (see Figure 13-12).

Figure 13-12. DCOUNTA() calculates the total number of invoices issued.

After you have calculated a total of 7,007 for all invoices, you can calculate the percentage of the invoices for companies within the United States. As you can see in Figure 13-13, you can calculate the percentage with the DCOUNTA() function.

Figure 13-13. The percentage of the invoices.

Although this method is tedious, it is possible. You know that 14 percent of your invoices go to companies in the United States. The rest of the invoices are sent to companies outside the United States.