MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Replace Ctrl+Shift+Enter with Dynamic Arrays.


October 05, 2020 - by Bill Jelen

Excel Replace Ctrl+Shift+Enter with Dynamic Arrays.. Photo Credit: Tetiana Shevereva at Unsplash.com

Before dynamic arrays, people would use these crazy Ctrl+Shift+Enter formulas.

Say that you have a friend who is superstitious about Friday the 13th. You want to illustrate how many Friday the 13ths your friend has lived through. Before Dynamic Arrays, you would have to use the formula below.

Before dynamic arrays, the formula to calculate the number of Friday the 13ths between B1 and B2 is =SUMPRODUCT(
--(DAY(ROW(INDIRECT(B1&":"&B2)))=13),
--(WEEKDAY(ROW(INDIRECT(B1&":"&B2)),2)=5)) entered with Ctrl+Shift+Enter.

The same formula after dynamic arrays is still complicated, but less intimidating:


With dynamic arrays, the formula is =SUMPRODUCT(
(DAY(SEQUENCE(B4,,B3))=13)*
(WEEKDAY(SEQUENCE(B4,,B3),2)=5))
entered without Ctrl+Shift+Enter.

Another example from Mike Girvin's Ctrl+Shift+Enter book is to get a unique list.

A helper formula in D2 counts the number of unique items in a list. Then, a formula returns the unique list in D5. See the next screenshot for the formula.

Here is the formula. I won’t try to explain it to you.

This was the insane formula to get the unique list before Dynamic Arrays: =IF(ROWS(D$5:D5)>$D$2,"",
INDEX($B$2:$B$146,
SMALL(IF(FREQUENCY(IF($B$2:$B$146<>"",
MATCH($B$2:$B$146,$B$2:$B$146,0)),
ROW($B$2:$B$146)-1),
ROW($B$2:$B$146)-1),
ROWS(D$5:D5))))

The replacement formula with dynamic arrays is =UNIQUE(B2:B146).

Title Photo: Tetiana Shevereva at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.