Earlier I have used the MMULT array category function instead of DCOUNT to count values in each column in Google Sheets. This time, we can do that using either of the database functions, DCOUNT or DCOUNTA, in Google Sheets.
Since this blog contains more than 1k Google Sheets tutorials, you may require to depend on the Search to find the above MMULT tutorial. To avoid that, here is the link to quickly go through the MMULT solution.
Related: Count Multiple Columns and Get the Count Separately in Google Sheets.
Introduction
I have realized quite recently that Database category functions in Google Sheets are useful to produce array results. I have already written a few tutorials in line with that.
Let’s see how to use a single DCOUNT/DCOUNTA formula to count values in each column in Google Sheets. Here is the scenario.
Scenario:
In one of my sheets, I have a table with six columns.
What I want is something like this – count the values in each column using a single formula (an array formula).
We can do it individually using the count functions COUNT or COUNTA. Further to include conditions, we can use COUNTIF, COUNTIFS, or QUERY.
For example, to count all the non-empty cells in B2:B (please refer to the below table/image), we can use the below non-array Count formula.
=counta(B2:B)As per my example, the above formula is in cell I4. So, you may drag it to N4 to return the count of values (non-empty cells) in each column.
To count only the checked boxes in B2:B, use COUNTIF as below in cell I7 and drag it to N7.
=countif(B2:B,TRUE)But to count values in each column in a table in Google Sheets, we can use a single formula. Not individual formulas as above for each column.
As I have already mentioned above, we are going to use the DCOUNT/DCOUNTA database function here. The MMULT is an alternative, and I have already left the related post link above.
Count Values in Each Column in a Table Using DCOUNTA in Google Sheets
In the above example, I have DCOUNTA formulas only in cells I3 and I6.
Note:- DCOUNTA is useful to count all values (including text). If you are dealing with numeric value columns, you can use both functions. But in mixed type columns, to count only numeric values, then use DCOUNT.
Let’s see how to write DCOUNTA to count values in each column in Google Sheets.
There are two DCOUNTA formulas. One includes a condition, but another doesn’t.
Let’s start with the latter one.
Count All Non-Empty Cells in Each Column Using Dcounta
As you may know, the syntax of DCOUNTA is as below.
DCOUNTA(database, field, criteria)As per my table, the database is B1:G, the field is B1:G1, and the criteria is also B1:G.
In my formula, I don’t want to include any condition. So I have referenced the table as it is as the criteria.
So the Dcounta formula to count values in each column in a table without any conditions will be as follows.
=ArrayFormula(dcounta(B1:G,B1:G1,B1:G))It will return only the count values in I4:N4. If you want the field labels in I3:N3 with the result, then use the below formula.
=ArrayFormula({B1:G1;dcounta(B1:G,B1:G1,B1:G)})The ArrayFormula use is a must as we want the DCOUNTA to return the result from six columns.
Dcount to Count Only a Specific Value in Each Column
The key here is modifying the database in the Dcounta formula.
The database is B1:G. It covers all the values. I want to count the checked boxes in each column using a single DCOUNTA formula.
For that, in the above formula, replace B1:G with the below logical expression.
{B1:G1;if(B1:G=TRUE,1,)}It converts all the TRUE values to 1 and FALSE values to blank. So the table only contains numeric values. Hence, you can here use DCOUNT or DCOUNTA.
So the formula without field labels will be;
=ArrayFormula(dcounta({B1:G1;if(B1:G=TRUE,1,)},B1:G1,B1:G))And with field labels will be;
=ArrayFormula({B1:G1;dcounta({B1:G1;if(B1:G=TRUE,1,)},B1:G1,B1:G)})Additional Tips
Point # 1:
In all the above formulas that count values in each column in Google Sheets, the criteria are B1:G. That means no conditions are specified.
Its usage is equal to referring two blank cells vertically or using the equivalent IF logical expression below.
{if(,,);if(,,)}Point # 2:
The conditional DCOUNTA uses the below formula as the database.
{B1:G1;if(B1:G=TRUE,1,)}It helps Dcounta to count checked values (Boolean TRUE) in each column in Google Sheets.
The TRUE is not within double quotes similar to a string, as it’s a Boolean value equal to numeric value 1.
Point # 3:
If you want to count the unchecked values, change TRUE to FALSE.
Based on your condition, change the above database in your Dcounta formula.
That’s all about how to count values in each column in Google Sheets using a D’ formula.
Thanks for the stay. Enjoy!
Additional Resources:
