You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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.
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, 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.
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, 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, the second element in the array is TRUE. That is, the second cell of the Seller range, 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,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...
Comments: (12) Collapse
Cool.
Charly, did you know you can also do this with either sumif, sumproduct, sumifs, and/or pivot tables?
The final link "Continue reading on Office.com" is extremely misleading.
Because the text behind the link seems to be a general reference to Office.com, I presumed all that this link was saying was the equivalent of "You'll find more great stuff on this topic at Office.com".
In fact the link was to the same article but this time in full.
1. Why not make this clear by saying. "Read the whole of this article here" as the text for the link?
2. Why in fact have any part of this article as the Blog? If you had a very short blog that just said "Go to xxxx for a long article on Excel's Array Formula" the whole thing would be much clearer.
@Mike: Thanks for your feedback. We updated the link text to make it a little clearer, and it now jumps directly to the "More examples of array formulas" section so that it picks up where you left off. It also loads the complete article in a new browser window so that you don't lose your connection to the blog.
Is it a joke?
On MS site the array formulas are in 2010 promoted??
This is a terrible way to get what is to get with simple database function, not meaning vlookup or sumif.
When MS will see, that Excel user isn't stupid?
It not jumps directly to the "More examples of array formulas" section and it not loads the complete article in a new browser window. :o(
(Sorry for my english)
You guys are all missing the point.
"Subtotals can reveal very useful management information. "
> We updated the link text to make it a little clearer, and it now jumps
> directly to the "More examples of array formulas" section so that it
> picks up where you left off. It also loads the complete article in a new > browser window so that you don't lose your connection to the blog.
Thanks Gary.
All three are very sensible improvements to the original link. It's hard to imagine a better fix in fact.
@Barbara: By any chance, are popup windows disabled in your browser? Or is it possible that you hadn't refreshed your page since I made the changes to the link? In the new URL for the link, should you should see a "#more" hash mark at the end.
Charly,
a) Stick to Dashboards
b)Use Array formulas sparingly,typically to do things that cannot be done without array formulas
Eg. Find the amount next to a second instance of a particular item - would need a array formula.
All the above formulas can be replaced with Sumproduct (or SUMIF(s)/Index)
It is really tragic to see a MS publishing such articles....What is really strange is that it is coming from an MVP -who as a rule are generally far better that the people at MS
For some really nice things on array formulas read these articles from
Bob and Tushar.
www.emailoffice.com/.../arrays-bobumlas.html
www.tushar-mehta.com/.../array_formulas.htm
Hi,
nice article, thanks :-)
However, the link to office.com leads to an error (page does not exist) as the the link tries to redirect to the german section of office.com. My suggestion is to directly link to the article on Office.com
office.microsoft.com/.../summarize-spreadsheet-data-with-excel-s-array-formulas-HA102131266.aspx
Regards
i have learnt too much from your sit. i like your lessonn
very much.thank you sir......
Sum product is very good....
Comments: (loading) Collapse