search
HomeCommon ProblemHow to sum irregular merged cells in excel

How to sum irregular merged cells in excel

Jun 25, 2023 pm 02:35 PM
excelsum summation function

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.

How to sum irregular merged cells in excel

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

How to sum irregular merged cells in excel

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)

How to sum irregular merged cells in excel

② Use the sum function skillfully. Let’s look at the function demonstration first.

How to sum irregular merged cells in excel

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

How to sum irregular merged cells in excel

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

How to sum irregular merged cells in excel

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

How to sum irregular merged cells in excel

Method 2: sumifs function method

sumifs is really Useful functions

How to sum irregular merged cells in excel

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

How to sum irregular merged cells in excel

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!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

MantisBT

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

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment