BYCOL

BYCOL takes two arguments: an array to process and a function to run on each column. The function can be written in two ways. The first way is as a lo

=BYCOL(array,LAMBDA(col,SUM(col)))
=BYCOL(array,LAMBDA(col,AVERAGE(IF(col<>0,col))))
=BYCOL(array,LAMBDA(col,MAX(col)-MIN(col)))

The second way is a short-form "eta lambda" syntax. Instead of wrapping the function in a LAMBDA, you provide just the function name. This works for s

=BYCOL(array,SUM) // sum each column
=BYCOL(array,MAX) // max of each column
=BYCOL(array,AVERAGE) // average of each column
=BYCOL(array,COUNTA) // count of non-empty values

In the worksheet below, the goal is to sum the test scores in each subject column of the range C5:H14. The formula in C16 is:

=BYCOL(C5:H14,SUM)

BYCOL processes all 6 subject columns at once and returns an array of 6 totals that spill into C16:H16. Notice we are using the short eta syntax above

=BYCOL(C5:H14,MAX) // max of each column
=BYCOL(C5:H14,MIN) // min of each column
=BYCOL(C5:H14,AVERAGE) // average of each column

BYCOL is useful when you want to count values per column that meet one or more specific conditions. This is where the eta syntax breaks down and the f

=BYCOL(C5:H14,LAMBDA(col,SUM(--(col>90))))

Zeros sometimes mean "no data" rather than "a real value of zero" (a student who missed an exam, a sensor that failed, a store that wasn't open, etc.)

=BYCOL(C5:H13,LAMBDA(col,AVERAGE(IF(col<>0,col))))