EXCL Filter sum

Filtering in Excel is a very common feature. But without knowing whether it was intentional or negligent, Excel did not directly provide some statistical functions after filtering, such as summing, averaging, etc. And because one of the main functions of the screening is to easily and quickly transform, the ordinary to directly in the data at the bottom of the line to sum can not be achieved, the calculated value is not accurate.

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



Table 2: Filter and select the "non-intrusive" and "total" two options in the name item. This time the total (980) is still the total of 10 items in table 1, rather than the single total (420) that appears to be non-intrusive.



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




Table 4: This time again to filter, in the name of the "Do Not Disturb" option, you can draw the correct results.

Table 5: In addition to summing, the subtotal function can also be used to count, minimum, maximum, calculate the average and other statistical functions. However, for multiple classification statistics at the same time, you need to select the appropriate name when filtering.

Remark: Subtotal function

Category: Mathematics and Trigonometric functions

Returns subtotals in a list or database. In general, you can easily create a list with subtotals by using the Subtotals command on the Data menu. Once you have created subtotals, you can modify the list by editing the SUBTOTAL function.

Grammar

SUBTOTAL (_num,ref1,ref2,...)

_num is a number that specifies what function to use for subtotal calculations in the list.

REF1, Ref2, can be from 1 to 29 for a range or reference to be calculated for subtotals.

Description

1. The SUBTOTAL function applies only to vertical areas and not to data rows or horizontal areas.

2, the meaning of _num numbers:

2.1 101 AVERAGE (average)

2.2 102 count (value count)

2.3 103 COUNTA (inclusion text count)

2.4 104 Max (max.)

2.5 min (min)

2.6 106 Product (product of all numbers)

2.7 107 STDEV (estimating the standard deviation of the sample, reflecting the degree of divergence relative to the mean)

2.8 108 STDEVP (returns the standard deviation of the entire sample population. It reflects the degree of dispersion of the sample population relative to the mean.)

2.9 109 sum (sum)

2.10 VAR (Estimating variance based on a given sample)

2.11 111 VARP (calculates the variance based on the population of a given sample)

3, if in Ref1, Ref2,... There are already other subtotals (nested subtotals) in, these nested subtotals are ignored to avoid duplicate calculations. That is, the results obtained by subtotal in the data area are ignored!

This feature is also available for those who prefer to use the "Subtotal" chart. We know that when there are subtotals, the calculation totals are the most likely to produce duplicates, but using the SUBTOTAL function does not have this disadvantage. For example, table 6, where subtotals and subtotals use the SUBTOTAL function, are obviously not duplicated.




Continue to use the filter on table VI, select "Non-interference" and "subtotal" in the name, the table 7, but also no repeated calculations.

4, the _num number can be from 1 to 11, or from 101 to 111, the difference is that if there is a manual hidden row, the former will still calculate the value of manually hidden rows, and the latter will ignore the number of manually hidden rows. However, for those values that are automatically hidden after filtering, both will be ignored.

Http://wenku.baidu.com/link?url=quLmltLt4dP3Cp87xjtsy7G8Md-PuhKHw6bhJCf92rpGD4B6HBKogtUUTbfGZo4G_ Ubj2lzixvisbu-iznozdzqoxp6cqncmooofsvra_ea

EXCL Filter sum