Hi - Dave here.
Happy Friday!
What is Excel's REDUCE function good for?
I spent the last couple of weeks gathering some examples to try to answer that question.
In a nutshell, while I think REDUCE works nicely for certain conditional counts and sums, I think REDUCE is most useful for complex text operations.
A good example is the worksheet below, where we use REDUCE to extract and count the unique words in a range of text, with help from the GROUPBY function. The formula in cell D5 looks like this:
=LET(
rng,B5:B16,
words,DROP(REDUCE("",rng,LAMBDA(a,v,VSTACK(a,TEXTSPLIT(v,," ")))),1),
GROUPBY(words,words,COUNTA,0,0,-2))
[
Download the workbook and read the full explanation]
Setting up REDUCE takes some practice, because it relies on a custom LAMBDA function to perform calculations. But once you get the hang of it, it's not too bad. The hardest part is recognizing situations where REDUCE would be useful. To help you out, the article above contains 9 examples with detailed explanations, including basic examples. Download the workbook and try it out yourself.
Note: REDUCE is only available in Excel 365 and Excel 2024.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at a specific Excel formula or function. Sign up on our home page.