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

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:

=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

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

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.