How to Use the BYCOL Function in Google Sheets

Published on

The BYCOL function in Google Sheets applies a custom LAMBDA function to each column in an array. Unlike row-wise operations, BYCOL groups the array by columns and processes them separately. If the provided array has five columns, the output will also contain five columns unless wrapped in another function that returns a single-cell result.

How Did We Handle This Before?

Before the BYCOL function in Google Sheets was introduced, achieving similar results required dragging formulas across columns or using workarounds such as MMULT, QUERY, and database functions.

Example: Summing Each Column Using a Database Function

Consider the following dataset in A1:F7, where:

  • A1:B7 contains cost centers (expense allocation codes) and years.
  • C1:F7 contains sales data for Q1, Q2, Q3, and Q4.

To sum each quarter, you might have used the following DSUM formula in C8, which expands across columns:

=ArrayFormula(DSUM(C1:F7, {1,2,3,4}, C1:C7))
Google Sheets database function (DSUM) used to sum values across multiple columns

Why Move Away from Database Functions?

While database functions work well for aggregation and lookup, they have limitations. If you wanted to apply a SPARKLINE or FORMULATEXT formula to each column, you would have had to manually drag the formula across columns. Additionally, database functions require structured data, which may not always be ideal.

This is where the BYCOL function in Google Sheets fills the gap, allowing you to apply custom formulas to each column dynamically.

BYCOL Function Syntax and Arguments

Syntax:

BYCOL(array_or_range, lambda)

Arguments:

  • array_or_range – The array or range processed column by column.
  • lambda – A custom unnamed LAMBDA function applied to each column.

LAMBDA Syntax:

=LAMBDA(name, formula_expression)

Step-by-Step Guide: Using the BYCOL Function in Google Sheets

Let’s replace the DSUM formula from earlier with BYCOL and LAMBDA.

Steps:

  1. Use a regular formula to total a column:=SUM(C2:C7)
  2. Convert it into a custom LAMBDA function:=LAMBDA(col, SUM(col))Here, col is the assigned name for each column.
  3. Apply BYCOL to process all columns at once:=BYCOL(C2:F7, LAMBDA(col, SUM(col)))

Now, instead of manually dragging formulas, BYCOL automates the process dynamically.

Google Sheets BYCOL function used to sum values across multiple columns

BYCOL Function Examples

Here are more cases of use for the BYCOL function in Google Sheets.

1. COUNTIF for Each Column

Count values greater than 5 for each column:

=BYCOL(C2:F7, LAMBDA(col, COUNTIF(col, ">5")))

2. COUNTIFS for Each Column

Count values greater than 2 and less than 4:

=BYCOL(C2:F7, LAMBDA(col, COUNTIFS(col, ">2", col, "<4")))

3. Expanding SPARKLINE Results with BYCOL

Generate a column chart for each quarter’s sales:

=BYCOL(C2:F7, LAMBDA(col, SPARKLINE(col, {"charttype", "column"; "max", 30; "color", "red"; "empty", "zero"})))

Do I Need ARRAYFORMULA with BYCOL?

You may encounter #VALUE errors when using BYCOL in Google Sheets. If the error message states “An array value could not be found,” you likely need ARRAYFORMULA.

Example: Counting Blank Cells with ARRAYFORMULA

=ArrayFormula(BYCOL(C2:F7, LAMBDA(c, SUM(--(ISBLANK(c))))))

This formula counts blank cells in each column of C2:F7 and returns an array result like {0,1,0,0}.

Key Takeaways

  • The BYCOL function in Google Sheets automates column-wise calculations.
  • It simplifies repetitive formulas and eliminates the need for dragging across columns.
  • Works best for SUM, COUNTIF, SPARKLINE, and other column-wise functions.
  • If you face #VALUE errors, use ARRAYFORMULA for array-dependent functions.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.