Excel irregular merged cell summing method: 1. Sum function, enter the sum() function and area in each merged cell area; 2. Sumifs function method, the condition area of sumifs cannot be merged Cell, enter the function in the area that needs to be summed, select Quick Fill Ctrl Enter, and the sum can be realized.
The operating environment of this tutorial: Windows 10 system, Excel 2016 version, DELL G3 computer.
In Excel, if you require sum, then the sum function and sumifs function will be indispensable. If you are exposed to many functions, you will also think of the subtotal function. First, let’s simulate the data environment and requirements. As shown below, you need to calculate the total sales volume of each person in the merged cell of the total sales volume
The next step is to formally share
Method 1: sum Function
①Traditional method, enter the sum() function and area in each merged cell area. If there is less data, you can do this, but if there is more data, the efficiency will be very poor (not recommended)
② Use the sum function skillfully. Let’s look at the function demonstration first.
Let’s decompose this function: the first sum is Sum all the values in column D (the summation area is D4 to D23), and the second sum is the sum of the area excluding the first (the summation area is E5 to E23)
Why does this happen? What is the effect? I need to add some knowledge to you. Please look at the picture below
When merging cells, only the first cell will have numbers, and the second one will be as shown above. is empty, use this feature
to see how the first merged cell 681 is calculated: SUM(E4:E23) is equivalent to SUM(F4:F23), and the sum and subtraction are exactly You can get the merged summation result
So how to write the function? You definitely can’t enter them one by one. At this time, you need to use a shortcut key Ctrl enter for quick filling. Please pay attention to the GIF operation below, select the data area, enter the function, and press Ctrl enter
Method 2: sumifs function method
sumifs is really Useful functions
It should be noted that the conditional area of sumifs cannot be merged cells, that is, I canceled the merged cells in column B in the above picture. Similarly, first select the merged summing area E4:E23, then enter the function in the cell, select quick fill Ctrl enter, and you can achieve it. For details, please see the picture below
Notes
Pay attention to the absolute reference and relative reference of the sum function in the first method
The above is the detailed content of How to sum irregular merged cells in excel. For more information, please follow other related articles on the PHP Chinese website!