Calculation summary in Excel filtering state

This article brings you relevant knowledge about excel. It mainly introduces how to add serial numbers after filtering, multiply after filtering, and count according to conditions after filtering. Let’s take a look at it together. I hope it helps everyone.

Related learning recommendations: excel tutorial

1. Add the serial number after filtering

as shown in the figure below, To maintain continuous serial numbers in the filtered state, we can cancel the filter first, enter the following formula in cell D2, and then pull down:

=SUBTOTAL(3,E$1:E2)-1

The SUBTOTAL function only counts visible cell contents.

Using 3 for the first parameter indicates the calculation rule for executing the COUNTA function, that is, counting the number of visible cells for the second parameter.

The second parameter uses a dynamically expanded range E$1:E2. As the formula is pulled down, this range will become E$1:E3, E$1:E4, E$1:E5,...

The formula always calculates the number of visible non-empty cells in the area from the first row of column E to the row where the formula is located. Subtract 1 from the result, and the calculated result will be the same as the serial number, and it will remain continuous after filtering.

Note, please note that if this formula is changed to =SUBTOTAL(3,E$2:E2), that is, starting from the row where the formula is located, the serial number result will be fine, but the last row will be filtered by Excel. Always displayed as a summary row.

2. Multiply after filtering

As shown in the figure below, after filtering column E, you need to calculate the total amount multiplied by the unit price.

The formula in cell E2 is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*F4:F16*G4:G16)

To calculate the filtered product, the key to the problem is to determine whether the data is visible.

How to judge this visible status?

Need to combine OFFSET and SUBTOTAL functions.

First use the OFFSET function, taking cell E3 as the base point, and offset rows 1 to 13 downwards in order to obtain a multi-dimensional reference. This multi-dimensional reference contains 13 reference areas with one row and one column, that is, individual cells from E4 to E16 are referenced respectively.

Next use the SUBTOTAL function, use 3 as the first parameter, that is, count the number of visible cells in each cell from E4 to E16 in sequence. If the cell is in the display state, count this cell. The result is 1, otherwise the statistical result is 0. Get a memory array with the following effect:

{1;0;1;1;1;1;0;0;1;1;0;1;0}

Use the above again The result is multiplied by the quantity in column F and the unit price in column G. If the cell is in the display state, it is equivalent to 1*quantity*unit price, otherwise it is equivalent to 0*quantity*unit price.

Finally use the SUMPRODUCT function to sum the products.

3. Count according to conditions after filtering

As shown in the figure below, after filtering the departments in column E, the number of people with more than 3 years of service must be calculated.

The formula in cell E2 is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E3,ROW(1:13),))*(G4:G16>3))

The first half of the calculation principle is the same as the previous example, and the core is to determine whether the cell is visible.

The statistical conditions in the second half of the formula (G4:G16>3) are multiplied by the judgment results in the first half, indicating that both conditions are met at the same time, that is, the number of items in the visible state and column G is greater than 3 .

4. Automatically correct the title after filtering

As shown in the figure below, after filtering the department name in column E, you want the title of cell D1 to automatically change to the corresponding department name. The formula is:

=LOOKUP(1,0/SUBTOTAL(3,OFFSET(D1,ROW(1:15)-1,)),E:E)&”Statistics Table”

The combination of SUBTOTAL and OFFSET functions still aims to determine whether the cells in column D are visible. Get a memory array composed of 0 and 1:

{0;1;0;0;0;0;1;1;1;1;0;1;0;1;0}

Use the memory array 0/ to get a new memory array composed of 0 and error value:

{#DIV/0!;0;#DIV/0!…;0;0;0 ;0;#DIV/0!;0;#DIV/0!;0;#DIV/0!}

The LOOKUP function uses 1 as the query value to find the position of the last 0 in the above memory array , and returns the contents of column E at the corresponding position.

The ultimate goal is to extract the content of the last displayed cell after filtering.

Connect the extracted content with "statistical table" and turn it into an automatically updated table title.

Related learning recommendations: excel tutorial

The above is the detailed content of Calculation summary in Excel filtering state. For more information, please follow other related articles on the PHP Chinese website!