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!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

SublimeText3 Chinese version
Chinese version, very easy to use

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version
Useful JavaScript development tools

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment