The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.

You can actually create more values than the original size using the REDUCE function, this article shows how:
Workaround for the TEXTSPLIT function – LAMBDA function

1. REDUCE Function Syntax

REDUCE([initial_value], array, lambda(accumulator, value, calculation))

Back to top

2. REDUCE Function Arguments

Back to top

3. REDUCE Function example 1

This example shows how to count values in a cell range that meet a specific condition. If a number begins with 1 the accumulator argument is increased by 1.

This is a simple demonstration of the REDUCE function, I know that the formula can be made a lot smaller using only the SUM function and the LEFT function.

Formula in cell D3:

Explaining the formula

Step 1 - Get first character from left

The LEFT function extracts a specific number of characters always starting from the left.

Function syntax: LEFT(text, [num_chars])

LEFT(val)

becomes

LEFT(745)

and returns "7".

Step 2 - Compare character with "1"

The equal sign lets you compare values in an Excel formula.

LEFT(val)="1"

becomes

"7"="1"

and returns FALSE.

The equal sign is a logical operator, the result is TRUE or FALSE.

Step 3 - Add acc by 1 if logical expression evaluates to TRUE

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(LEFT(val)="1",acc+1,acc)

becomes

IF(FALSE,acc+1,acc)

and returns acc which is 0 (zero) to begin with.

Step 4 - Pass two parameters to function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(acc,val, IF(LEFT(val)="1",acc+1,acc))

Step 5 - Pass all values in cell range B3:B12

REDUCE(0,B3:B12,LAMBDA(acc,val, IF(LEFT(val)="1",acc+1,acc)))

returns 4.

4 numbers begin "1" in cell range B3:B12.

Back to top

4. REDUCE Function example 2

This formula lists unique distinct "Regions"  from column C and populates the first column of the returning array, the remaining columns are populated by values from the "Text" column based on the adjacent "Region" value.

Formula in cell E3:

Explaining the formula

Step 1 - Compare Region to values in cell range C3:C12

The equal sign is a logical operator, the result is TRUE or FALSE. It lets you compare values in an Excel formula.

val=C3:C12

Step 2 - Filter values in cell range B3:B12 based on condition

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B12,val=C3:C12)

Step 3 - Transpose values vertically to horizontally

The TRANSPOSE function converts a vertical range to a horizontal range, or vice versa.

Function syntax: TRANSPOSE(array)

TRANSPOSE(FILTER(B3:B12,val=C3:C12))

Step 4 - Add array to acc

The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)

Function syntax: VSTACK(array1,[array2],...)

VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12)))

Step 5 - Create a LAMBDA function in order to use the REDUCE function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))

Step 6 - List unique distinct values from cell range C3:C12

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(C3:C12)

becomes

UNIQUE({"East";"South";"South";"East";"North";"West";"South";"West";"South";"North"})

and returns

{"East";"South";"North";"West"}

Step 7 - Pass each unique distinct value to the LAMBDA function

REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12)))))

First iteration: {"",#N/A,#N/A,#N/A}

All iterations stacked vertically:

{"",#N/A,#N/A,#N/A;"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A}

The first row in the resulting array is a blank value and the remaining values are error values. This row is not needed.

Step 8 - Remove first row in the array

The DROP function removes a given number of rows or columns from a 2D cell range or array.

Function syntax: DROP(array, rows, [columns])

DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1)

becomes

DROP({"",#N/A,#N/A,#N/A;"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A},1)

and returns

{"EVA LFO","THR DQY",#N/A,#N/A;"RQW WPQ","HNL RYN","UNW NUM","BHZ ONF";"HYJ WXA","JZM UXJ",#N/A,#N/A;"WEA HGU","NXE OGF",#N/A,#N/A}

Step 9 - Stack unique distinct values and the resulting array horizontally

The HSTACK function combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)

Function syntax: HSTACK(array1,[array2],...)

HSTACK(UNIQUE(C3:C12),DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1))

returns

{"East", "EVA LFO", "THR DQY", #N/A, #N/A;"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", #N/A, #N/A;"West", "WEA HGU", "NXE OGF", #N/A, #N/A}

Step 10 - Remove error values

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

IFERROR(HSTACK(UNIQUE(C3:C12),DROP(REDUCE("",UNIQUE(C3:C12),LAMBDA(acc,val,VSTACK(acc,TRANSPOSE(FILTER(B3:B12,val=C3:C12))))),1)),"")

becomes

IFERROR({"East", "EVA LFO", "THR DQY", #N/A, #N/A;"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", #N/A, #N/A;"West", "WEA HGU", "NXE OGF", #N/A, #N/A}, "")

and returns

{"East", "EVA LFO", "THR DQY", "", "";"South", "RQW WPQ", "HNL RYN", "UNW NUM", "BHZ ONF";"North", "HYJ WXA", "JZM UXJ", "", "";"West", "WEA HGU", "NXE OGF", "", ""}

Back to top

Get the Excel file

How-to-use-the-REDUCE-function.xlsx

Back to top

'REDUCE' function examples

Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

Convert date ranges into dates
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]

Filter words containing a given string in a cell range
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]

Functions in 'Logical' category

The REDUCE function function is one of many functions in the 'Logical' category.

Excel categories

Leave a Comment