Quick and automatic table sum/average/count in Excel

Direct summation

Sometimes, in addition to the total data, we also need to Subtotal the items in some tables, as shown in the figure below:

Collapse the image and expand the image.

 

Batch fill in the Subtotal results in the red area in the figure above. If the table structure has been formed, and then delete the Subtotal rows using the classification summary method, it is indeed a bit redundant. This can be done with a simple operation:

Collapse the image and expand the image.

 

 

Collapse the image and expand the image.

 

What is the effect? Climb into the cell and try again. ^_^

In addition, this function is only available for Summation. Other statistics, such as counting, average, maximum, and minimum, all have errors.

Collapse the image and expand the image.

 

In this case, you can use a work und:

Collapse the image and expand the image.

 

Replace the valueAverage, ReplaceMax...... What is the effect? Continue to the sentence: climb into the cell and try again ^_^

In addition, this operation is not limited to Version 2010, Version 2003 and version 2007 are also applicable, but only the"Go"In the menu"Edit"Positioning"Positioning conditions.

There are also some general shortcut keys available for all versions, such as the shortcut keys for positioningCtrl + GOrF5, Shortcut for automatic summationALT + equal sign.

Function method

Sometimes, in addition to the total data, we also need to Subtotal small projects in some tables, as shown in the figure below:

Collapse the image and expand the image.

 

Everyone on earth knows how to useSUMThe sum can be entered, for example, in cells B7:= SUM (B2: B6)

Press enter to obtain the result.C7: E7You only need to fill in or copy to the right.B7Paste it.

Collapse the image and expand the image.

 

But what about 11th rows? SetB7After the formula is directly pasted, it cannot be used ......

Therefore, we cannot rely on a simpleSUMTo solve the problem, but use the following formula:

= SUM (B $2: B6)-SUMIF ($ A $2: $ A6, "SUBTOTAL", B $2: B6) * 2

Collapse the image and expand the image.

 

This formula is actually a wonderful use of absolute reference and relative reference, andSumIfFunction.

First LookB7Formula:

= SUM (B $2: B6)-SUMIF ($ A $2: $ A6, "SUBTOTAL", B $2: B6) * 2

This formula is well understood:

SUM (B $2: B6)ComputingB2: B6The sum of values in these five cells.A2: A6"Subtotal", SoSumIfPartial result:0;B2: B6And0The result is exactly what we need.

Check againB11Because absolute reference does not change the address, and relative reference changes the formula with the address of the cell where it is located. After copying, the original formula becomes:

= SUM (B $2: B10)-SUMIF ($ A $2: $ A10, "SUBTOTAL", B $2: B10) * 2

SUM (B $2: B10)YescomputingB2: B10The sum of the values in the nine cells is divided into three parts:B2: B6All projectsATotal,B7That isAClass subtotal, andB8: B10That is, the sum of all project B, and we only need the third part: all projectsBAnd the first two parts need to be subtracted; in fact, all projectsASum andASUBTOTAL is the same thing, so we need to subtract it. We can think of it as twoAClass subtotal, whileSumIfThe formula just calculatesASUBTOTAL ,;B2: B10And minus twoAClass subtotal, the result is exactly all the projects we needB.

The same applies to other formulas.

However, even with this formula, copying and pasting one line at a time is also a kind of physical activity. You can use the positioning method to process it more quickly:

Collapse the image and expand the image.

 

 

Collapse the image and expand the image.

 

The final sum, of course, is used= SUM (B1: B6, B8: B10...... So tired, you can simplify it:

Formula:= SUM (B2: B28)/2Or:= SUMIF ($ A2: $ A28, "SUBTOTAL", B2: B28)

Collapse the image and expand the image.

 

How can I climb to the cell and try again? ^_^