Excel Function: BYCOL

The BYCOL function applies a LAMBDA function to each column and returns an array of results.

Prerequisite: understand the LAMBDA function.

Usage:

=BYCOL(array, LAMBDA)


Usage example

The objective here is to calculate with a single formula the total for each column:

excel best supplier bycol

Start by entering the BYCOL function followed by the array that contains the prices:

=BYCOL(B2:D9

Then add the LAMBDA function and choose a variable name (for example "price"):

=BYCOL(B2:D9,LAMBDA(price

And finally, add the SUM function to get the total:

=BYCOL(B2:D9,LAMBDA(price,SUM(price)))

The BYCOL function will then go through the entered array (B2:D9) column by column and return the sum of each column:

excel functions bycol lambda sum
If needed, you can download the Excel file used here: bycol.xlsx
Note: this function is only available with Office 365.