Shulou(Shulou.com)11/24 Report--

The original title: "complete the data collection in 1 minute!" These three Excel summation methods are not wrong (recommended collection). "

One of the most commonly used methods of summary statistics in Excel is summation.

Each company has different requirements for peace. Some sum the rows in the data area, and some sum the column data. There are some summation of the data of the cross position of the rows and rows, and some are misplaced summation.

The data structure is also very different.

Well, a man sent a question for help, as shown in the following picture:

The requirement is: the handling fees will be counted and summed up according to the project category. As shown below:

For example, for regular training fees, you need to count all the fees under the regular training cell listed in [B]. The cells of [D3] and [D5] are to be summed.

The service charge for venue rental is the same.

1. Problem analysis when I first saw the problem, I was a little confused!

In general, we may record data like this. As shown below:

Add the name of the project to which you belong before the service fee.

If we record the data in this way, it will be exactly the same as our usual routine of using the summation function.

The formula used is as follows:

= SUMIF (Borel BMagF2 MagneD) Formula Analysis:

In the [B] column, find the contents of the [F2] cell, and sum the amount in the corresponding cell in the [D] column if the condition is met.

The problem now, however, is that they do not record the data as shown in the figure above.

If you directly use the SUMIFS double-conditional summation, the result will be as follows.

The result is 0!

The formula is as follows:

= SUMIFS) what is the problem with BJV BJL left (F2Magol 4), BRV B, "handling fee")? How on earth can we make peace?

2. ▋ method to solve the problem 1: SUMIFS multi-conditional summation according to the above problem analysis, the original table recorded like this is indeed a double-conditional summation problem.

That is, the double condition here is a wrong line summation.

Let's change the above formula to the following:

The formula is as follows:

= SUMIFS ($B$3:$B$8, "handling fee") Formula Analysis:

There are two conditions for SUMIFS multi-condition summation.

The first pair of conditions is: "$Baud2VOLIFE (F2PINE4)".

In the cell [$B$2:$B$7], find the contents of the first four characters in the [F2] cell.

That is, regular training. (here, use the LEFT function to intercept the four characters to the left of the [F2] cell)

The second condition pair is: "$B$3:$B$8,"handling fee".

In the cell [$B$3:$B$8], look for the contents of the service charge.

If the condition is met at the same time, sum the corresponding row amount in the [$D$3:$D$8] cell range.

The second condition pair here and the summation region of [$D$3:$D$8] are exactly one line wrong from the first condition.

▋ method 2: SUM array formula here we can change the two conditions into a condition by using the method of misplaced connection of text connectors, and then multiply it with the summation region.

The formula is as follows:

= SUM (($B$2:$B$7&$B$3:$B$8=F2) * $D$3:$D$8) formula parsing:

The idea here is the same as that of SUMIFS's multi-conditional summation above.

In order to make you understand, we will add two auxiliary columns to demonstrate, as shown in the following figure:

In the figure, we copy the [B3:B7] cell to [C2:C6]

Copy the [E3:E7] cell to [F2:F6].

If arranged in this way, it is exactly the same as the conventional summation routine.

$B$2:$B$7&$B$3:$B$8 results as follows:

{"regular training fee"; "fee venue rental"; "venue rental fee"; "handling fee"}

The result is exactly the same as the connection between [B2:B7] and [C2:C7] in the auxiliary column diagram.

Then compare it with the content in the cell [F2] (regular training fee), and return TRUE if equal, FALSE otherwise.

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

Finally, multiply by the amount in the $D$3:$D$8 cell.

TRUE is equivalent to 1m false is equivalent to 0.

The results are as follows:

{- 10 / 10 / 0 / 10 / 10 / 10 / 10 / 10 / 10 / 10 / 10 / 0 / 0 / 0}

Finally, the result is obtained by SUM. Namely:-20.

3. Knowledge expansion here we share another way to solve the problem, that is, the PivotTable method.

From the original record above, it belongs to an one-dimensional data table and can be processed with a powerful PivotTable with a little change.

As shown below:

Add an auxiliary column to the [E] column and enter the following formula in the [E2] cell:

= IF (B2 = "handling fee", E1MagneB2) Formula Analysis:

If the [B2] cell is equal to the service charge, return the contents of the [E1] cell, otherwise return the [B2] cell itself.

The meaning of the formula is very simple. The main purpose is to make the fee belong to the item content of the cell above it.

Then, we select this area as the data source, then click "insert"-"PivotTable"-bring up the PivotTable dialog box, and click "OK" directly.

In the newly generated worksheet, drag the Secondary column and Project to the row area, and the amount to the value area. As shown below:

In this way, the general statistics are completed.

Make the following settings, and click "Design"-"report layout"-"display in tabular form".

Finally, you can filter the fees in the project.

4. at the end of today, we share a very special method of summation: summation in the wrong line.

❶ uses SUMIFS multi-condition wrong row summation.

❷ uses the SUM array formula to sum the wrong rows.

❸ uses the method of auxiliary column + PivotTable to sum the wrong rows.

Of course, in the actual work, we will also encounter staggered summation, interlaced summation, intercolumn summation and so on.

You can refer to today's article to solve the problem.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.