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

If a person is unlucky, writing a SUM function can report an error.

-SUM function I can, relatively absolute reference I can also!

-but if you put it together, it won't!

Yes, I'm talking about merging cells for summation.

For example, in the following table, you want to sum in column D merge cells, and the formula looks like this:

= SUM (C2:$C$20)-SUM (D3:$D$20)

Can you understand this formula?

Forget it if you don't understand it! We have a better way.

Asking everyone to write this formula is just like letting teacher Dong Yuhui and Li Jiaqi learn to sell goods.

Dong Yuhui is a teacher who is good at teaching, lecturing, quoting and passing knowledge. He can't do it if he is forced to learn Li Jiaqi's OMG style of selling goods.

We usually just fill in the numbers with the form, check the data, and print the report. It's too difficult for you to write such a complicated formula.

I have come up with a way to make the sum of merged cells very simple. After improvement, the formula looks like this.

= P_SUM_BY_HEBING (C2)

A P_SUM_BY_HEBING function to solve the problem, no relative absolute reference, no subtraction, just a function, this is not a mess!

The 2P_SUM_BYHEBING function automatically identifies the range of merged cells and sums them.

It has several parameters:

Sum_cell: yes. The cell that requires the sum

Just select one cell in order to get the column in which the summation cell is located. Excel automatically determines the area of summation.

Hebing_cell: optional. Merged cells.

The merged cell to be judged when summing.

If this parameter is not set, the default is the cell in which the formula is currently written.

Is_by_col: optional. Whether merged cells are merged by column

Indicates whether the merged cells are merged by column. There are two results:

-1. Default: indicates that cells are merged, which are merged by column.

-0. Represents merged cells, which are merged by row.

Let's take a look at a few examples and we can understand it right away!

1-merge cell summation according to merge cell summation, the basic usage, the formula is as follows:

= P_SUM_BY_HEBING (A100)

In the formula, P_SUM_BY_HEBING knows that it is the sum of column A based on the first parameter.

Because the current formula is in cell B2, this is a merged cell in lines 2-3.

At this point, P_SUM_BY_HEBING automatically recognizes that the region of the sum is the area of column A, row 2-3. Namely A2:A3, and carries on the summation calculation.

2-in the following case, merge the cell sum according to column B, and fill in the calculation result in column C.

= P_SUM_BY_HEBING (C2 ~ A2)

The principle of calculation is similar:

The 1-1st parameter determines that the required sum is the cell of the C column.

2-the second parameter, identifying that the summation line is line 2-3.

In this way, the summation region is C2:C3 cell, and the result is returned to D2 cell after calculation.

Interestingly, the P_SUM_BY_HEBING function automatically recognizes the merged cells in the first parameter, as long as this cell is in the merged cell range.

3-the horizontal merge cells are summed up by the following formula, and the horizontal merged cells are summed quickly.

You can sum the horizontally merged cells by setting the third parameter to 0.

= P_SUM_BY_HEBING (B1Person0)

The usage of the formula is exactly the same. Excel automatically recognizes the merged cells and sums them.

Summarize how to get the function? The code of the P_SUM_BY_HEBING function is as follows, copy and paste it into your VBA, and you can use it.

Function P_SUM_BY_HEBING (ByVal sum_cell As Range, Optional ByVal hebing_cell As Range, Optional ByVal is_by_col As Integer = 1) Dim thisCell As Range, new_area As Range, first_cell As Range, cells_count As Integer Dim sr As Long, sc As Long, fr As Long, fc As Long If Not hebing_cell Is Nothing Then Set thisCell = hebing_cell Else Set thisCell = Application.thisCell End If Set first_cell = thisCell.MergeArea.Cells (1 1) sr = sum_cell.Row sc = sum_cell.Column fr = first_cell.Row fc = first_cell.Column If thisCell.MergeCells Then If is_by_col = 0 Then cells_count = thisCell.MergeArea.Columns.Count-1 Set new_area = Range (Cells (sr, fc), Cells (sr) Fc + cells_count) Else cells_count = thisCell.MergeArea.Rows.Count-1 Set new_area = Range (Cells (fr, sc), Cells (fr + cells_count, sc)) End If P_SUM_BY_HEBING = WorksheetFunction.Sum (new_area) Else P_SUM_BY_HEBING = sum_cell End IfEnd Function this article comes from the official account of Wechat: la Xiaodeng (ID:ladengchupin) Author: bin Laden Dony

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.