00:00Can you imagine being in a situation where you have to sum
00:02these particular numbers on a merge cell like this?
00:06A typical sum function like this, it's not gonna work.
00:10Well, it's gonna work for the first cell, but if you drag like that,
00:13it's not gonna work really well for you because the cells are merged together.
00:17So how do you get around it? Here's what you can do.
00:20The first thing I have to do is basically select the cell where you want to add the sum function
00:24to,
00:25and then after that, press CTRL-1 to open cell formatting pop-up here.
00:30Make sure you're on the number tab and make sure you select a general and then click on OK.
00:35That will essentially format all these three cells into a general format.
00:39That's essential for this particular formula to work.
00:42Next, you're gonna select the same set of cells again and then basically enter the following formula
00:48and then press CTRL-Enter.
00:51Let's do a little bit of testing here.
00:53Let's start from the center record here.
00:57Let's change this very first number to 2
00:59and make sure that summation changes and maybe change this one to 1
01:04and over here to 11 and over here to 2.
01:08Perfect. They are all working.
01:10So let me explain how this formula actually works.
01:13But before we do that, there's one thing you have to know.
01:16On a merge cell, the value is always stored on the top left-hand side of the merge.
01:21What that means is that, let's say for example, if I say equal D5,
01:27it will give you a value of 31.
01:29But if you do equal D6, which is right below here,
01:35D6 like this, it will give you a value of 0.
01:38And likewise, D7 will give you 0.
01:40And over here, if you go D8, another one more example,
01:44it's a equal D8, you will get a value of 23.
01:47And if you do D9, you get 0 and so on.
01:51I think you get the point.
01:52So with that being said, let's go back to the formula here.
01:55The formula simply calculates the difference
01:57between the sum values of the first range
02:00and the sum values in the second range.
02:03And that's how the formula works.