Como usar a fórmula DCOUNT no Planilhas Google

O DCOUNT fórmula no Google Sheets é quase semelhante à do COUNT formula, but with a distinction. It gives us the count of numeric values available in a table like range, that meet a specified criteria. This is analogous to an SQL database count query. The ‘D’ in the DCOUNT stands for ‘Database’, therefore, we can call this a Database COUNT formula.

Sintaxe

DCOUNT(database, field, criteria)

• banco de dados – é a referência a um intervalo de dados estruturado que consiste em rótulos, para cada coluna, na primeira linha.
• campo – indicates the column on which the DCOUNT formula should count the numeric values. This can be a text or a column index too.
• critérios – this is a reference to a range that consists of criteria that the formula uses to filter the banco de dados values before counting.

Usage: DCOUNT Formula

Let us try our hands with a few examples on a sample data set picked up from this site.

Vemos que o conjunto de dados está em formato tabular rotulado, abrangendo as células A4 até G13. E os critérios estão inseridos nas células A1 até G2.

Please note from the cases illustrated above that we can specify one or more criteria to filter the counting process. For example, the case in row # 8 counts the numeric values in the “Fat (g)” column, subject to the criteria in the cell F2. Whereas the case in row # 10 counts numeric values from “Energy (Kcal)” column, subject to the criteria within the cells C2 and D2. The last case on row # 13 includes all the criteria from C2 through to G2, and apparently there is no data that satisfies all of these conditions, hence the result 0.

The case on the row # 9 is an interesting one! We didn’t specify anything in the cell G2, yet, we gave that reference to the DCOUNT formula. The result is 9, because it counted everything in the “Fiber (g)” column as there is no criteria to filter out the rows.

Unwarranted Cases

Now, let us talk about pitfalls. Here is the first one, which we might already be aware of. Please consider the first case in the snapshot below.

Here, we tried counting non numeric values! Just like the COUNT formula, DCOUNT formula counts only numbers. Therefore, it returned ‘0’ as output.

The second is a not so obvious as the first one. Seemingly there’s nothing wrong with the formula this time. But the problem lies within the labels. Apparently, a missing or a mismatched label doesn’t help the DCOUNT formula as its working hinges on the field names that the second parameter takes. Since the labels didn’t match, it returned the count as zero.

DCOUNT formula

And there you go! Use the DCOUNT formula in Google Sheets to count the numeric values available in a table like range, that meet a specified criteria.

Se quiser saber mais sobre as várias fórmulas do Planilhas Google, dê uma olhada em nossa postagem no blog sobre o DCOUNTA formula in Google Sheets.

Como alternativa, confira as postagens relacionadas no blog abaixo!