MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


September 30, 2019 - 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


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.