Spreadsheets: Many Formulas into One with a Top-Secret Move

There's a secret class of formulas in which Excel will calculate many values and hold all of those values in the computer memory before summarizing the array of results.

=SUM(IF(TEXT($A$2:$A$13,”MMMM”)=D3,1,0))

Do not press Enter. Instead, hold down Ctrl Shift and then press Enter. Excel will add curly braces around the formula in the formula bar to indicate that this formula is being evaluated as an array as shown in Figure 2.

Fig. 2

14MrExcel-Fig02

 

Array Formulas Are Difficult to Copy

Notice the dollar signs in $A$2:$A$13 are designed to allow the formula to be copied down to cells E4:E6. There is one curious limitation to copying this formula. Normally, I would select E3, press Ctrl C, then press Ctrl Shift Down Arrow to select E3:E6 and press Ctrl V to paste. You cannot copy using these steps. The paste range cannot include the original formula cell. As a result, you have to do the following:
• Select E3 and Copy;
• Select E4:E6 as the paste range;
• Paste.

More Confusion from Array Formulas

After reading this article, you might create an array formula and pass that workbook on to a co-worker. He will likely be confused by this formula but might try to replicate the logic in another cell. Why? First, people see the curly braces around the formula in the formula bar and think they are supposed to type these when they enter their own formula. That will not work. You have to know Ctrl Shift Enter.

Second, if someone edits the formula, he has to remember to press Ctrl Shift Enter in order to finish editing the formula. If he simply presses Enter, Excel will evaluate only the first cell in the array and the answer will be 1 for September and 0 for the other months.

Summing Amounts Instead of Counting

I’m going beyond Curtis’s question, but what if you need to sum the amounts associated with the dates? Rather than using 1 as the result of the IF formula, you could instead insert B2:B13 as an array of results, as shown in Figure 3 (below).

Fig. 3

14MrExcelFig03-C.gif

 

Array formulas are a confusing concept. If you are new to them, you might want to see other examples of array formulas in these articles: “Use a CSE Formula (Array Formula) to Perform Super-Calculations on Data in Excel” and “Use a Special Excel Array Formula to Simulate SUMIF with Two Conditions.”

Bill Jelen, a CFO contributing editor, runs MrExcel.com and is the author of 32 books about Excel, including Guerilla Data Analysis Using Microsoft Excel. You can win a copy of one of his books if your question is selected for a column. Post your question to the community content block on the right.

Discuss

Your email address will not be published. Required fields are marked *