Ravindra BagaleCourses & study guides Track your progress

Excel BYCOL Function

By · Last updated:

Chala mitrano — today we learn BYCOL. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 with LAMBDA.
Fictional sample data for learning. Fictional monthly sales across regions.

Purpose

Run a LAMBDA once per column of an array and spill one result per column.

Return value

A row (usually) of per-column results.

Syntax

BYCOL(array, lambda)

Arguments

ArgumentRequired?Description
arrayRequired2D range or array.
lambdaRequiredLAMBDA(col) returning a result for that column.

Version

Microsoft 365 with LAMBDA.

How to use — Why → What → How

Why: Column totals across months without copying SUM for each month.

What: Each column vector goes into your LAMBDA.

How — numbered steps:

  1. Example: =BYCOL(B2:G20, LAMBDA(c, SUM(c))).
  2. Use for monthly totals across a wide sheet.
  3. VSTACK/HSTACK to place results under or beside headers.
  4. Clear spill blockers to the right.
  5. Mirror skills from BYROW.

Ravindra Bagale's Tip

Wide month sheets → BYCOL. Tall student sheets → BYROW.

Ravindra Bagale's Tip

Keep one result per column for a neat spill row.

Worked examples

1) Month totals

Jan–Jun in B2:G50.

Formula: =BYCOL(B2:G50, LAMBDA(c, SUM(c)))

Result: 6 monthly totals

What it means: One spill row.

2) Column averages

Ratings.

Formula: =BYCOL(B2:E40, LAMBDA(c, ROUND(AVERAGE(c),1)))

Result: Averages

What it means: Rounded means.

3) Count of blanks per column

Import QA.

Formula: =BYCOL(A1:F100, LAMBDA(c, COUNTBLANK(c)))

Result: Blank counts

What it means: Cleaning scorecard.

4) Max per market column (£)

UK regions.

Formula: =BYCOL(B2:D30, LAMBDA(c, MAX(c)))

Result: Peaks

What it means: Maxima row.

5) % of column total via LET

Share — advanced.

Formula: =BYCOL(B2:D10, LAMBDA(c, LET(t, SUM(c), IF(t=0,0, INDEX(c,1)/t))))

Result: First-row share

What it means: Shows LET inside BYCOL.

Common errors

#SPILL!: Cells to the right blocked.
#CALC!: LAMBDA shape.

Practice tasks (10+)

  1. Task 1. BYCOL SUM on a 10×6 block.
    Hint: try on a tiny range first
  2. Task 2. BYCOL AVERAGE.
    Hint: try on a tiny range first
  3. Task 3. Place results under headers with VSTACK.
    Hint: try on a tiny range first
  4. Task 4. Count numbers vs text per column.
    Hint: try on a tiny range first
  5. Task 5. Interview BYCOL vs BYROW.
    Hint: try on a tiny range first
  6. Task 6. Combine BYCOL with ROUND for ₹.
    Hint: try on a tiny range first
  7. Task 7. Detect empty columns.
    Hint: try on a tiny range first
  8. Task 8. Fix #SPILL!.
    Hint: try on a tiny range first
  9. Task 9. BYCOL MAX/MIN pair with VSTACK.
    Hint: try on a tiny range first
  10. Task 10. Bonus: BYCOL of a FILTER result.
    Hint: try on a tiny range first

Quick quiz

Q1. BYCOL walks columns, not rows.
Q2. BYCOL needs LAMBDA.
Q3. Available in Excel 2013.
Q4. Good for month-wide totals.

Answers: Q1=True, Q2=True, Q3=False, Q4=True

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path