×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

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

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close