INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join TekTips Forums!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
 Students Click Here
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here

Best of Excel
Summarize data with a single equation by JVFriederick
Posted: 20 Apr 01 (Edited 28 May 03)

So you have Excel data and you need to summarize in multiple ways? I would like to try and explain a method that will take more effort, but the technique can be used in so many ways it is WELL WORTH the trouble.
Assume a table of data as follows :
A B C D 1 Date Client Amount Budget 2 4/19/01 Name1 2,000 1,500 3 4/19/01 Name2 1,000 5,500 4 4/19/01 Name3 3,000 1,000 5 3/17/01 Name1 5,000 6,000 6 3/17/01 Name2 4,000 3,500 7 3/17/01 Name3 1,500 2,500 8 3/17/01 Name4 2,000 1,500
Now construct your analysis table starting in cell B10 as follows :
B C D 10 4/19/01 3/17/01 11 Name1 12 Name2 13 Name3 14 Name4
Now in cell C11, type the following equation (you can paste the formula from this posting). This formula assumes you want to summarize by the "Amount" column. I'll explain the formula a little later.
=SUM(($A$2:$A$8=C$10)*($B$2:$B$8=$B11)*$C$2:$C$8)
Go ahead and press Enter, and you will get a #Value!  but don't worry. Now press F2 key (edit) and then press Ctrl+Shift+Enter at the same time. If you need to edit the formula in the future, always use Ctrl+Shift+Enter. Now the formula should look like this :
{=SUM(($A$2:$A$8=C$10)*($B$2:$B$8=$B11)*$C$2:$C$8)}
Now you should have those little {} on each end of the formula, and the result shows 2000 in cell C11. Highlight C11:D14 and then press Ctrl+D then Ctrl+R, which copies down and then copies to the right. (BTW, Ctrl+D & Ctrl+R works great for copying cells in one easy step.) Now your table is complete.
OK, so this seems pretty weird ?
There are 3 parts to the equation. The first part looks in column A2:A8, which has Dates. Compare that to the date you entered in C$10. Note that $10 is used to freeze the row reference. The second part looks in column B2:B8, which has Clients. Compare that to the client you entered in $B11. Note that $B is used to freeze the column reference. The third part looks in column C2:C8, which has Amount, which is what we wish to Sum. If a match is found in both the first and second parts of the equation, then the values in column C are added.
This analysis technique is maybe a bit much for this task, but can easily be expanded to handle 3, 4, or many more summation levels. Example : $4000 for Name1 on 4/19/01 that falls in accounting period 3 and is being managed by Tom Jones who reports to Henry. To summarize such a combination, just add Accounting Period and Salesman and Manager to the table, and then add the new column references to the formula. You can easily summarize all sorts of combinations.
Likewise, by dropping the second part of the equation, which is Clients, will give a subtotal by Date.
=SUM(($A$2:$A$8=C$10)*$C$2:$C$8)
The formulas shown above refer to variables within the speadsheet that refer to the borders of the analysis table. However, if you have the need to calculate just a single "answer", simply enter the parameter.
=SUM(($B$2:$B$8="Name1")*$C$2:$C$8)
One last thing. If the position of the column "variables" line up with the main data table columns, it's a lot easier to troubleshoot and to understand.
Give it a try. If you can get the hang of it, you'll be able to summarize almost anything by writing a single equation.

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum 


