Excel in the filter, is a very common function. But do not know whether it is intentional or negligent, Excel does not directly provide some of the statistical functions after filtering, such as summing, averaging, and so on. And because the screening of one of the main functions is that you can quickly and easily transform, the ordinary in the data directly below the line can not be achieved, the calculated value is not accurate.

Table 1: Enter a simple chart, a total of 10 three categories, the bottom line of the total.

Table 2: Screening, and in the name of the item selected 'You Are the One' and 'Total' two options. The sum of the time (980) is still the sum of the 10 items in Table 1, rather than the individual items that seem to be ugly. (420).

Table 3: At this time, you need to use the subtotal function, in the C13 cell input formula: = SUBTOTAL (109, $ C $ 2: $ C $ 11)

Table 4: This time to filter, in the name of the item selected 'You Are the One' option, you can get the

correct results.

Table 5: In addition to summing, the use of subtotal function can also count, minimum, maximum, calculate the average and other statistical functions. But for the same time a number of classification statistics, the need to filter the time, select the appropriate name of the item.

Remarks: SUBTOTAL function

\u0026 nbsp; \u0026 nbsp;

Category: Mathematics and trigonometric functions

Returns a summary of the data in the inventory or database. In general, you can easily create a list of data with subtotals using the Subtotal command in the Data menu. Once you have created a subtotal, you can edit the data list by editing the SUBTOTAL function.

grammar

SUBTOTAL (_num, ref1, ref2, ...) _num \u0026 nbsp; \u0026 nbsp; For numbers, specify which function to use for the subtotal calculation in the data list. Ref1, ref2, \u0026 nbsp; For regions or references to be aggregated for calculation, from 1 to 29.

Description: 1, SUBTOTAL function only applies to the vertical area, not applicable to the data line or horizontal area.

2, _num number of meaning:

2.1 \u0026 nbsp; \u0026 nbsp; 101 AVERAGE 2.2 \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; 105 MIN (minimum) 2.6 STDEV (the standard deviation of the estimated sample, reflecting the deviation from the relative value of the discrete degree) 2.8 \u0026 nbsp; \u0026 nbsp; 108 (minimum) 2.6 \u0026 nbsp; \u0026 nbsp; STDEVP (returns the standard deviation of the population as a whole, which reflects the degree of dispersion of the sample as a whole relative to the mean) 2.9 \u0026 nbsp; \u0026 nbsp; 109 SUM (Sum) 2.10 \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 nbsp; \u0026 gt; 110 VAR (estimated based on the variance of a given sample) 2.11 \u0026 nbsp; \u0026 nbsp; 111 VARP (Calculate the variance based on the given sample)

3, if there are other subtotal (nested subtotals) in ref1, ref2, ..., these nested subtotals are ignored to avoid double counting. That is, in the data area SUBTOTAL get the results will be ignored!

This feature also applies to those who prefer to use the 'subtotal' chart. We know that there is a subtotal, the calculation of the total is the most prone to duplication, but the use of subtotal function is not the drawbacks. For example, Table 6, subtotal and subtotal are used here are subtotal function, obviously no repeated calculation.

Continue to use the filter on the table 6, in the name selected 'You Are the One' and 'Subtotal', come to Table 7, is not repeated calculation.

4, _num numbers can be from 1 to 11, or from 101 to 111, the difference between the two is that if there is a manual hidden line, the former will still calculate the value of manually hidden lines, which will ignore the value of manually hidden lines. But for those values that are automatically hidden after filtering, both will be ignored.

Collection, and then slowly look.

Ref1, ref2, \u0026 nbsp; For regions or references to be aggregated for calculation, from 1 to 29.

How does this sentence understand? Is it listed as item 1 to 29?

No, it means that 29 regions can be calculated statistically

Has not been applied to practice, so I can not understand. Thank you for your advice.

Use several times just fine

Have you seen your information, can you introduce your learning method?

It will be the blessing of our vision. Thank you!

Brother is really too me, huh, huh

I used this function very smoothly.

Experience: the first line of empty, sum of the cells put the first line, the table can be too long to find the results look hard to find, say that subtotal is not displayed.

In the header line filter state, directly press 'Σ' is to filter sum, and then modify the calculation range to include all data lines, not when the filter is all the sum.

[Last post by myhway at 2011-1-12 22:25]

Thank you Ma brother to share.

Just try it, the key to solving the problem seems to be in the order.

If the first total after screening, it is not easy to use.

First screening and then total, it is very easy to use.

Thanks again. Also want to learn from the horse brother here more skills Kazakhstan.

Pass me a common style, pay attention to the first two lines.

In fact, such a running account can be used in many aspects.

Thank you,

copy content checker tool the collection

learned!