Ravindra BagaleCourses & study guides Track your progress

Excel REDUCE Function

By · Last updated:

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

Version note. Microsoft 365 with LAMBDA. Not in older perpetual Excel.
Fictional sample data for learning. Fictional sales and tip-pool numbers for learning.

Purpose

Accumulate a running result across an array with a LAMBDA (fold / aggregate).

Return value

A single value (or whatever your accumulator ends as) after walking the array.

Syntax

REDUCE([initial_value], array, lambda)

Arguments

ArgumentRequired?Description
initial_valueOptionalStarting accumulator (example: 0).
arrayRequiredValues to walk.
lambdaRequiredLAMBDA(acc, value) → new acc.

Version

Microsoft 365 with LAMBDA. Not in older perpetual Excel.

How to use — Why → What → How

Why: SUM is fixed. Sometimes you need a custom running rule (cap, skip blanks, product).

What: REDUCE keeps an accumulator and updates it for each item.

How — numbered steps:

  1. Start with =REDUCE(0, A2:A10, LAMBDA(acc,v, acc+v)) — same idea as SUM.
  2. Change the LAMBDA body for custom rules.
  3. Use initial_value so the first step is clear.
  4. Test on a tiny range first.
  5. Prefer SUM/PRODUCT when they already do the job — REDUCE for special logic.

Ravindra Bagale's Tip

Friends, if plain SUM works, use SUM. REDUCE is for custom folds.

Ravindra Bagale's Tip

Always name parameters acc and v (or total, item) so the fold reads clearly.

Worked examples

1) Custom sum

Sales B2:B5.

Formula: =REDUCE(0, B2:B5, LAMBDA(a,v, a+v))

Result: Total

What it means: Accumulator adds each value.

2) Product of quantities

Pack sizes.

Formula: =REDUCE(1, C2:C4, LAMBDA(a,v, a*v))

Result: Product

What it means: Initial 1 for multiply.

3) Sum only values over 100 (₹)

Ignore small lines.

Formula: =REDUCE(0, D2:D20, LAMBDA(a,v, IF(v>100,a+v,a)))

Result: Sum of big lines

What it means: Conditional fold.

4) Count non-blanks

Messy list.

Formula: =REDUCE(0, E2:E30, LAMBDA(a,v, IF(v="",a,a+1)))

Result: Count

What it means: Blank-safe counter.

5) UK running tip pool (£)

Add 10% of each bill into a pot.

Formula: =REDUCE(0, F2:F6, LAMBDA(a,b, a+b*0.1))

Result: Tip pool

What it means: Fold with a rate.

Common errors

#VALUE! / #CALC!: LAMBDA shape wrong (need two params: acc, value).
#SPILL!: Rare for scalar REDUCE; check if you nested arrays oddly.

Practice tasks (10+)

  1. Task 1. Reduce a list to a sum with REDUCE (then compare to SUM).
    Hint: try on a tiny range first
  2. Task 2. Reduce to a product.
    Hint: try on a tiny range first
  3. Task 3. Sum only odd positions using INDEX tricks or helper — or skip with IF.
    Hint: try on a tiny range first
  4. Task 4. Count text cells vs numbers.
    Hint: try on a tiny range first
  5. Task 5. Build a capped total: stop adding after 10,000.
    Hint: try on a tiny range first
  6. Task 6. Explain initial_value for multiply vs add.
    Hint: try on a tiny range first
  7. Task 7. Reduce nested with LET for the threshold.
    Hint: try on a tiny range first
  8. Task 8. Interview line: when REDUCE beats SUMIF.
    Hint: try on a tiny range first
  9. Task 9. Cause a LAMBDA arity error and fix it.
    Hint: try on a tiny range first
  10. Task 10. Bonus: REDUCE to concatenate with a comma (careful with length).
    Hint: try on a tiny range first

Quick quiz

Q1. REDUCE walks an array and updates an accumulator.
Q2. The LAMBDA for REDUCE usually has two parameters.
Q3. REDUCE is in Excel 2010.
Q4. Initial value 1 is useful for products.

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

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