Countif Across Columns Row by Row – Array Formula in Google Sheets

We all know the power of Google Sheets and how it simplifies our lives when it comes to data analysis and calculations. But there are times when we need to perform more complex operations, like counting across columns row by row. In this article, I will share with you some alternative solutions to achieve this using array formulas.

Introduction

You might be familiar with the Countif function, which allows us to count values that meet certain criteria. However, it doesn’t work as an array formula when it comes to counting across columns row by row in Google Sheets. So what’s the alternative?

The Problem

Let’s say you want to count the test scores that are greater than or equal to 10 (>=10) across columns in each row. You might be tempted to use the following Countif formula in cell F2 and copy it down:

=countif(A2:E2, ">=10")

While this formula works for a single row, it becomes cumbersome to manage when you have multiple rows with values to count. Every time you insert new rows, you’ll need to copy-paste the formula. That’s where array formulas come in handy.

Countif Across Columns Row by Row Using DCOUNT in Google Sheets

One way to achieve the desired result is by using the DCOUNT function. Before we dive into the formula, let’s understand the logic behind it.

DCOUNT Logic Used

The DCOUNT syntax is as follows: DCOUNT(database, field, criteria). To count values in a column using DCOUNT, we can use the following formula:

=dcount(A1:A10, 1, {if(,,);if(,,)})

By selecting two adjacent blank cells vertically as our criteria, we effectively count the values in a column without using any specific criteria. To count multiple columns (A, B, C, D, and E) using DCOUNT, we can modify the formula as follows:

=ArrayFormula(dcount(A1:E10, {1, 2, 3, 4, 5}, {if(,,);if(,,)}))

To count values conditionally in each column (values >= 10), we need to change the range to if(A1:E10>=10, A1:E10,):

=ArrayFormula(dcount(if(A1:E10>=10, A1:E10,), {1, 2, 3, 4, 5}, {if(,,);if(,,)}))

If you understand how to count values conditionally in columns using DCOUNT, you can easily grasp how to count across columns row by row. All you need to do is transpose the range from A2:E10 and use virtual field labels for the formula to work correctly.

Formula Explanation

Let’s take a closer look at the array formula to count across columns row by row in Google Sheets located in cell F2:

=ArrayFormula(if(A2:A="",,dcount(transpose({row(A2:A), if(A2:E>=10, A2:E, )}), sequence(rows(A2:A)), {if(,,);if(,,)})))

• Database: transpose({row(A2:A), if(A2:E>=10, A2:E, )})
  • We transpose the range A2:E10 to change the data orientation. This removes the field labels, but we still need them for DCOUNT to work. So, we include the row numbers of A2:A and combine them with the range if(A2:E>=10, A2:E,). Once transposed, the row numbers act as the field labels.
• Field: sequence(rows(A2:A))
  • Since we are counting across columns row by row, we need to count the total rows in the range A2:A. The sequence() formula returns the field numbers from 1 to n vertically.
• Criteria: {if(,,);if(,,)}
  • Since we don’t have a specific criteria, we use this formula to return two blank cells.
• Other Formula Parts: if(A2:A="",,
  • We use this IF test outside the DCOUNT formula because we have infinite rows in our database. This test helps us return the result only in the rows where column A has values.

Countif Across Columns Row by Row Using the BYROW Function (New)

Google has introduced a new function called BYROW, which allows us to expand a COUNTIF formula to conditionally count values across columns row by row. Let’s take a look:

=byrow(A2:E, lambda(r, if(counta(r)=0, , countif(r, ">=10"))))

This formula populates values in F2:F, provided cells in F3:F are blank. The BYROW function takes care of the array aspect, while the LAMBDA function specifies the condition for the COUNTIF.

In summary, whether you choose to use DCOUNT or the new BYROW function, you now have alternative solutions to count across columns row by row in Google Sheets. With these array formulas at your disposal, you can simplify your data analysis and make your life much easier!

Thanks for reading. If you want to learn more about Google Sheets and discover more tips and tricks, visit Crawlan.com.