Above, the criteria are fully contained with the formulas. But suppose you enter the value 5 in cell J3. You then could use these formulas:
You also could use array formulas to return the same results:
Notice that the two array formulas above sum ones and zeros rather than using a count function.
Notice that the two formulas above use null strings ("") rather than zero. If you were to use zero, the AVERAGE function would include those zeros in the average.
Using Non-Data and a Single Criteria
Until now, the examples have summarized data using criteria that applied to the data itself.
More frequently, however, we summarize data by applying our criteria to related columns of information that describe the data.
Here, for example, we find the number of units of Hats that have been sold, the number of hat orders, and the average order:
We could use array formulas to return the same results:
As before, "Hats" could be in a cell that the formulas could reference.
And again, notice that to count the number of hat sales, we we SUM an array of ones and zeros rather than using a COUNT function. And we also use a null string ("") rather than zeros for the AVERAGE-IF array formula.
Introducing SUMIFS, COUNTIFS, and AVERAGEIFS
So far, the examples have used only one criteria. But frequently, Excel users need to summarize data using multiple criteria. Excel 2007 introduced three new summary functions that allow for multiple criteria:
These functions work like SUMIF, COUNTIF, and AVERAGEIF, but they allow you to add any number of criteria. Here are some examples:
Array formulas also allow you to use multiple criteria.
Introducing Array Formulas With Multiple Criteria
Before you can use arrays successfully, you need to understand how they perform their calculations.
Array-enter the following formula in some cell. Then, in the formula bar, select the highlighted part of the formula:
With this piece of the formula selected, press the F9 key. When you do so, the formula changes to:
(You can use a similar approach to calculate one or more sections of any formula in your formula bar. After checking your formula, press Esc to return to your original formula. If you accidentally press Enter rather than Esc, just click on Undo in QAT.)
Now select Units in your formula bar and press F9 again. After you do so, the formula expands to:
Including the gray borders in the table, the database shown above and below has 15 rows of data. As a consequence, the green section of the formula has 15 TRUE and FALSE results, and the brown section has 15 numbers. Each TRUE in the green section marks where it's true that the product equals "Hats".
Press Esc to dismiss your changes in the formula bar. Now select the highlighted part of this formula:
Again, press F9. When you do so your formula bar displays:
Now compare the final orange section with the final green and brown sections above.
Notice that whenever the green section is TRUE, the orange section contains the corresponding value from the brown section; otherwise, the orange section contains zero.
From another perspective, notice that whenever the product is Hats in this figure, its number of units appears in the orange section above.
Finally, the SUM function returns the sum of the numbers in the orange section.
In general, this is how all array formulas work. They apply the logic of your array formula to each corresponding value in the multi-cell ranges that your formula references.
Don't worry if this idea isn't too clear yet, the following examples should help to give you a better understanding of how this works.
Using Formulas With Multiple Criteria
Let's take a simple example of how array functions can summarize multiple criteria:
From the previous discussion you already know that the Excel formula converts both Clients="Smith" and Colors="Blue" into a series of TRUE and FALSE values. But the gray section of the formula above multiplies these two arrays. You can see the results of this process if you calculate the gray section in your formula bar.
That is, after you array-enter the formula by pressing Ctrl+Shift+Enter, select the gray section shown above and press F9. When you do so, your formula bar will show this result:
Here, the array displays 1 only where the client equals "Smith" and where the color equals "Blue". Otherwise, the formula returns 0. The following display shows how this works.
Here, I've highlighted the four results in each array where the TRUEs are in sync. These results correspond with the four values of 1 found in the third row of the table.
Notice in the original array formula that each logical test is enclosed in parentheses. These parentheses are required. That is, within a multi-criteria array formula:
Here are some more examples of array formulas in action:
Notice in the last example above that you can perform operations on values in a column, and then compare the results to other values. Specifically, the green section of the formula finds the month value of each date and then compares it to 12.
However, this particular date test isn't very practical, because we typically need the month and year in a test like this...not merely the month. Here is a more practical date test:
(Notice that DATE(2008,1,0) returns the last day of December, 2007.)
In actual practice, you probably wouldn't include the DATE function in your formula, for two reasons.
First, the formula forces Excel to calculate the same date value for each cell in the array. This slows calculation slightly. Second, you would need to change the formula to change the dates you want to report.
Instead, you probably would enter the date functions in two cells of your spreadsheet. You could name the first cell FirstDate and the second cell LastDate. Your array formula then could be:
Using Array Formulas in a Report
So far, I haven't demonstrated the real value of using array formulas with multiple criteria: They allow you to set up standard reports that work much like PivotTables, but with more flexibility in many ways.
Here, for example, the actual report is in the range A7:D11. The area at the top of the page contains setup values and side calculations.
The two values highlighted in yellow act like PivotTable page fields. You can change their values to change the report.
Cell B5 checks the report for errors. If the grand total in cell D11 doesn't match the total of all sales for the specified client and month, the Error value turns TRUE, and the report title changes to "ERROR!!".
The key formulas for this report are shown with their cell addresses below. These formulas rely on names assigned to the range B1:B5. To assign these names, select the range A1:B5; choose Formulas, Defined Names, Create from Selection; in the Create Names dialog make sure that only Left Column is checked; then choose OK.
(You also can choose Ctrl+Shift+F3 to launch the Create Names dialog.)
Here are the key values and formulas:
Enter these values as shown. To format the date in cell B1, select that cell; in the Home tab, choose the Number group's dialog launcher; in the Number tab of the Format Cells dialog enter this format string as the Type value: mmm-yy; then choose OK.
(You also can choose Ctrl+1 to launch the Format Cells dialog.)
The array formulas that follow use the values returned by these standard formulas, as I described above.
This array formula finds total unit sales for the specified month and client, and then compares that value to the grand total in cell D11. If the values don't match -- if there's an error -- the formula returns TRUE; otherwise, it returns FALSE.
A7: =IF(Error,"ERROR!!","Unit Sales, "&Client)
Normally, this formula displays the current client name with the report title. But if there's an error, this formula returns "ERROR!!".
D7: =TEXT(ReportMonth,"mmmm yyyy")
This formula returns the date specified in cell B1. It uses the TEXT function to format that date as shown in the report. The cell is right-justified.
This array formula uses five criteria to return the correct value to the cell.
When you copy this formula to the rest of the report, you must paste it in two steps. This is because Excel doesn't allow a cell with an array to be copied to a range that includes the copied cell.
Therefore, copy cell B9 and paste it to cell D9. Then copy the range B9:C9 and paste it to the range B10:C10.
These formulas provide the row and column totals. Copy them down or to the right as needed.
Other Uses of Multi-Criteria Array Formulas
In old Excel, array formulas became really useful when you needed to use multiple criteria. But because new Excel offers three multi-criteria summary functions, you have a choice of whether to use the new functions or array functions.
But even if you choose to use the new functions, you should keep the power of arrays in the back of your mind. This is because you can use array functions for other types of calculations. Here are some examples:
Potential Problems with Arrays
Array formulas are very powerful. But they can have several problems:
Even with these potential problems, using arrays to summarize your data can reduce Spreadsheet Hell. This is because much of Spreadsheet Hell involves sorting and summarizing data. By using formulas like the ones shown here to summarize your data, you can begin to reduce the more time-consuming chores associated with Excel reporting.
The sample workbook with the data also includes the report described above. You can download the workbook using this link.