Excel 2007 Compartment Sum this can be more labor-saving

There are times when we need to do an interval summation of Excel 2007 worksheet data. If the work table format is not allowed or inconvenient to adjust, then how to smoothly and easily get the data we need?

This question is actually very simple. Many people who have just come in contact with Excel can also do it. Take the worksheet shown in Figure 1 as an example. We need to sum the data of C, E, G, I, and K in the worksheet. Just position the mouse over the M2 cell, and then enter the formula "=c2+e2+g2+i2+k2", and double-click the fill handle to automatically copy the formula down, even if you are done.

Figure 1

However, to do so your sense of accomplishment is certainly not strong, who will be the operation of what it means ah. At the very least, we have to use the function.

Then click the M2 cell, and then enter the formula "=sum (C3,E3,G3,I3,K3)" In the edit bar and press ENTER to get the M2 data. Then double-click its fill handle to copy the formula. Of course, we can also open the function Parameters dialog box by clicking the "Insert Function" button on the left side of the formula bar after entering "=sum ()". Then, in each "number" input box, enter the cells in which the data is located, as shown in Figure 2.

Figure 2

EXCEL2007 allows you to enter 255 of these data. Once you've identified it, you can get the data you need. This method is exactly the same as writing formulas directly in the formula bar.

But I'm afraid it's not much of a sense of achievement, and it's not likely to scare a lot of people. Even if you write the formula directly in the edit bar, the formula is too straightforward to show any level. If you need to accumulate too much data or let you help is a beautiful mm, it is too much? So, don't be the simplest, as long as the most cattle. So I directly in the formula bar to write an array of formulas, at least you have to directly let that mm guilty halo.

or locate the M2 cell, write the formula directly in the edit bar =sum (C2:L2) * (MOD (C2:L2, 2) =1), and then never enter, but press the "Ctrl+shift+enter" shortcut key, Add an array formula tag to the outermost of the formula (a pair of curly braces, remember that this tag cannot be entered manually), as shown in Figure 3. Hey, I don't believe that mm is not dizzy!

Figure 3

Simply explain: The column function can get the number of columns in the column, such as the formula "=column (C2)" To get the result is "3". The MOD function is the remainder, such as "=mod (9,2)", which computes the remainder of 9 divided by 2. Press the "Ctrl+shift+enter" shortcut key to get the array formula. The formula is ultimately calculated by multiplying each cell in the C2:l2 area by the remainder of the column in the current cell, divided by 2, and accumulating the resulting results. When the number of columns is even, the remainder is 0, so the summation is not counted.

So we can see that just because we want to add up the odd sequence, we use the MOD (COLUMN (C2:L2), 2) =1 in the formula. If you need to accumulate data in the even sequence, it should be changed to "MOD (COLUMN (C2:L2), 2) =0".

If you want to accumulate in two columns, then the remainder should be in addition to "3". As for "= 0", "= 1" or "= 2", it is necessary to carefully study the number of columns that need to accumulate data.

Well, at this stage, it's not much of a problem to bluff people. But then again, if you need to accumulate a lot of data, this formula is very useful.