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:
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:
If needed, you can download the Excel file used here: bycol.xlsx
Note: this function is only available with Office 365.