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.


Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO’s Spreadsheet Tips Page.

Curtis H. wins an autographed copy of Excel Gurus Gone Wild for his question: “How do you count dates by month in a range: if a column contains dates (formatted as 10/30/2010) and want to identify any/all in the range by specific month (October, November, etc.) and count the number of items in that range that occur in each month?”

I love this question from Curtis. It is a question that sounds simple, but actually requires one of the greatest Excel mysteries — the array formula. The small database in Figure 1 (below) illustrates the problem.

There are several good ways of converting a date to a month. For example:
=MONTH(A2) would return the month number of 11;
=TEXT(A2,”MMMM”) would return “November”; and
=TEXT(A2,”MMM”) would return “Nov”.

In addition, you could add a new column to your database that would convert the date to a month, then use COUNTIF to solve the problem. However, this solution would require one new formula for every row in your database.

Fig. 1

14MrExcel-Fig01

 

Ctrl Shift Enter: Let’s Excel Evaluate Super Formulas

Most people build normal Excel formulas that perform one calculation. There is a super-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.

These special formulas require you to hold down Ctrl Shift while pressing Enter after the formula. Those magic keystrokes of Ctrl Shift Enter cause Excel to expect that it will have to iterate at least one calculation through an entire range of values. Microsoft calls these formulas array formulas; I call them CSE formulas, because CSE helps me to remember the keystrokes of Ctrl, Shift, and Enter.

Above, I noted that =TEXT(A2,”MMMM”) would return “November.” In an array formula, =TEXT(A2:A13,”MMMM”) will return an array of month names: {November, September, October, September, November, September, December, November, October, December, September, November}.

If you compare this array to the value in D3, you would get a TRUE for every record that fell in September and a FALSE for every record from another month. =TEXT(A2:A13,”MMMM”)=D3 will return the values {FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.

If you wrap that array in =IF(,1,0), you will convert the TRUE values to 1 and the FALSE values to 0. =IF(TEXT(A2:A13,”MMMM”)=D3,1,0) would return {0; 1; 0; 1; 0; 1; 0; 0; 0; 0; 1; 0}.

From here, it is a simple matter to add up the number of 1′s returned in the array. Wrapping the whole formula in a SUM() function will solve the problem. Type this formula in cell E3:

Discuss

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