Microsoft Office Enterprise 2007 Summarize Spreads

Today’s author is Charley Kyd, an Excel MVP, who has worked with spreadsheets in business since 1979, and has written books on spreadsheet use for McGraw-Hill and Microsoft Press. In 1990, he also created the first-ever Excel dashboard. You can find more useful tips and posts from Charley and other Excel MVPs at his website, ExcelUser.com Subtotals can reveal very useful management information. For example, managers might be interested to learn that sales increased by 10% last month. But they would be fascinated to learn that Pat Smith's sales of Widgets in the Northwest region doubled last month, while the sales of all other people fell sharply. Array formulas provide a way by which Excel users can discover such useful information. Introducing the Data To explain the power of array formulas I'll use this database. It shows sales by Seller, Product, Region, and Customer, with Quantity and Total Sales, in dollars. I named each column of data with the label shown in row 1. To do so, I selected the range A1:F15; chose Insert, Name,Microsoft Office 2010 Home And Business, Create; chose Top Row; and then chose OK. By assigning names in this way we anchor the names in the gray border rows, rows 2 and 15. By doing so, we can add new data to this table between the gray rows and be confident that the names will expand as needed. Introducing Array Formulas, Example 1 Let's begin our examination of this data by summarizing the sales for Jill and Joe. Cells J3 and J4 perform this summary by using array formulas. Here's the formula for the cell shown: J3: {=SUM(IF(Seller=$I3,Total,0))} Notice the braces that surround this formula. You do NOT enter those characters when you key in the formula. Instead, you type the formula shown within the braces. But when you're done typing you don't press Enter. Instead,Microsoft Office 2010 Standard, you hold down the Ctrl and Shift keys, then press Enter. After you do so, Excel displays the formula in the formula bar with the braces, as shown above. Here's the key to understanding how array formulas work: Each array formula creates temporary arrays in memory, and then the outside function returns the results from that array. To illustrate, the "Seller=$I3" part of the formula creates a temporary array like this: {FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE; FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} Here,Microsoft Office 2007 Standard, the second element in the array is TRUE. That is, the second cell of the Seller range,Microsoft Office Enterprise 2007, cell A3 contains the text "Joe". The formula says that wherever TRUE appears, return the corresponding value from the Total column. Therefore, the statement
"IF(Seller=$I3,Total,0)" returns the temporary array: {0;12600;0;0;0; 5060;0;1980;0;1540;0;4500;0;0} Finally, the SUM function returns the sum of this temporary array: 25,680. The second formula is similar: J3: {=SUM(IF(Seller=$I4,Total,Microsoft Office Pro 2010,0))} You can copy this formula from cell I3, or enter it for the practice. Again, if you enter it, do NOT manually enter the braces. Instead, when you enter the formula using Ctrl-Shift Enter, the braces will appear automatically. Continue reading this post on Office.com... <div