Summarize data with a single equation

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 C1114 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.