The BYCOL function in Google Sheets helps us to get aggregation results in each column of an array or range.

If the provided array contains five columns, the output will also be in five columns in a row.

So you can use this innovative function to spill across the results of SUM, MIN, MAX, COUNTIF, COUNTIFS, SPARKLINE, ISBLANK, etc., in Google Sheets.

I have, in the recent past, been using the database functions for such operations in Google Sheets. Here is an example that involves the DSUM.

=ArrayFormula(dsum(C2:F8,{1,2,3,4},C2:C8))

But it has two main limitations.

1. There is no way to expand an outside function result, e.g., a SPARKLINE result, as no such database function is available.
2. Structured data is a must.

The BYCOL is one of the LAMBDA helper functions (LHF), and its usage is similar to BYROW, another LHF.

They differ at one point. The former takes a column, whereas the latter takes a row as the name argument in LAMBDA.

If the array reference is A1:Z10, the name argument will usually be A1:A10 for BYCOL and A1:Z1 for BYROW.

The former returns a column-wise array result (expands across), whereas the latter a row-wise array result (expands down).

Let’s see how to use the BYCOL function to simplify certain aggregations in Google Sheets. Here we go!

BYCOL Function Syntax and Arguments in Google Sheets

BYCOL Syntax:

BYCOL(array_or_range, lambda)

Arguments:

array_or_range – The array or range to be separated by a column (C3:F8 as per the example [screenshot # 1] above).

lambda – A LAMBDA that takes a column, usually the first column in the array_or_range (C3:C8 as per the example [screenshot # 1] above), as a single name and calculates one result.

The BYCOL function will expand it in the given array_or_range.

LAMBDA Syntax: =LAMBDA([name, ...],formula_expression)(function_call, ...)

To learn this relatively NEW function, please check my function guide.

Important (W.r.t. LAMBDA):- 

1. The functioin_call argument is not required as it’s for standalone LAMBDA use.
2. The LAMBDA takes more than one name argument, but with the BYCOL function, we must use only one name argument.

How to Use the BYCOL Function in Google Sheets

Scroll up and see the DSUM formula.

We can use the BYCOL function in Google Sheets to replace that.

Please check the syntax for the two arguments. They are array_or_range and lambda.

As per our sample data, here are them.

array_or_range – C3:F8

lambda – lambda(c,sum(c))(C3:C8) which is equal to SUM(C3:C8).

Here is the BYCOL formula to replace the above DSUM.

=bycol(C3:F8,lambda(c,sum(c)))

You require to insert this formula only in cell C10. It will expand to F10, provided D10:F10 is blank. Otherwise, you will see a #REF error.

Please note that we have omitted the LAMBDA function_call in the BYCOL formula. You must follow that when coding your own.

Formula Examples

Here are a few more examples to help you understand how to use the BYCOL function in Google Sheets.

We will use the same array or range, i.e., C3:F8, in the examples.

1. COUNTIF for Each Column (C11):

The following BYCOL formula will count the values greater than five (>5) and return the output for each column.

=bycol(C3:F8,lambda(c,countif(c,">5")))

You May Like: How to Use All Google Sheets Count Functions [All 8 Count Functions]

2. COUNTIFS for Each Column (C12):

When we want to count the values greater than two and less than four, we can use COUNTIFS within the LAMBDA as below.

=bycol(C3:F8,lambda(c,countifs(c,">2",c,"<4"))) 

3. BYCOL Function to Expand a SPARKLINE Result (C13):

We may be able to meet our above requirements without the BYCOL function in Google Sheets.

But as far as I know, it was impossible to expand a SPARKLINE formula result earlier.

Now it’s possible and see the below SPARKLINE Column Chart formula that returns four column charts for Q1, Q2, Q3, and Q4 values.

=bycol(C3:F8,lambda(r,SPARKLINE(r,{"charttype","column";"max",30;"color","red";"empty","zero"})))

As a side note, I’ve used the following BYROW formula in cell G10 (refer to screenshot # 2 above) to expand a FORMULATEXT formula.

=BYROW(C10:F13,lambda(f,FORMULATEXT(f)))

ArrayFormula with BYCOL Function

When you experiment with the BYCOL function in Google Sheets, you may encounter #VALUE errors at some point in time.

If the tooltip says, “An array value could not be found,” use the ArrayFormula function.

Here is an example of using ArrayFormula with the BYCOL function in Google Sheets.

=ArrayFormula(bycol(C3:F8,lambda(c,sum(--(isblank(c))))))

The formula returns the count of blank cells in each column in the array C3:F8, which would be 0, 1, 0, and 0.