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.