Automatic table summation using function method in Excel2010

Sometimes, in addition to the total data, there are some small items in the table to be subtotal, the following figure:

Earth people know that sum can be summed, such as in the B7 cell input: =sum (B2:B6)

Press ENTER to come to the results, the back of the c7:e7 as long as the right to fill or copy B7 paste the past can be.

But the 11th line? The B7 formula can not be used directly after the AH ...

So, instead of using a simple SUM to solve the problem here, you need to use the following formula:

=sum (B$2:B6)-sumif ($A $: $A 6, Subtotal, B$2:B6) *2

This formula is actually a magical use of absolute references and relative references, as well as SumIf functions.

First look at the B7 formula:

=sum (B$2:B6)-sumif ($A $: $A 6, Subtotal, B$2:B6) *2

The formula is well understood:

SUM (B$2:B6) is b2:b6 of the values in these five cells, because there is no "subtotal" in the A2:a6, so the SumIf part of the result is 0; B2:b6 and plus 0, the result is exactly what we need.

Look at the B11 formula, because an absolute reference does not change the address, and a relative reference makes the formula vary with the address of the cell in which it is copied, and the original formula becomes:

=sum (B$2:B10)-sumif ($A $: $A 10, Subtotal, B$2:B10) *2

SUM (B$2:B10) is B2:B10 that calculates the values in these nine cells, this is divided into three parts: B2:b6 is the sum of all item A, the B7 of Category A, and the sum of all project B B8:B10, and we only need the third part: the sum of all project B, the first two parts need to try to subtract; in fact, the sum of all project A and a subtotal is one thing, so the need to subtract, can be regarded as two category A subtotal, and SumIf formula, just calculate a category a subtotal,; B2:B10 and minus two category A subtotals, the result is the sum of all the project B we need.

The other formulas have the same analogy.

But even with this formula, a single line of copy-and-paste is also a manual activity, which can be handled more quickly with the positioning method:

The final total, of course, with =sum (B1:b6,b8:b10 ...). Too tired, can simplify:

Formula: =sum (B2:B28)/2 or: =sumif ($A 2: $A 28, Subtotal, b2:b28)

How does the effect crawl into the cell yourself try ^_^