Office hacks: achieve dynamic summation and cumulative summation of tables

In the course of the actual office, we often need to dynamic summation of data by month, cumulative summation. If you can use SUM, OFFSET, MATCH and other functions, the above tasks are very easy to achieve.

Tip: The following operations are done in Excel 2019.

Let’s take the monthly sales table shown in the figure (Figure 1) as an example. First, create drop-down menus for “Name”, “Start Month”, “End Month”, etc. Select cell F14, switch to the “Data” tab, click “Data Validation → Data Validation”, in the pop-up window under the “Settings” tab, in the “validation conditions” of the “Allow” at the choice of “series”, “source” at the choice of A2:A11 cell area, so that when you click on cell F14 will appear on the name drop-down menu. Similarly, in cell F15, F16 also data validation settings, “source” at the selection of B1: M1 cell area, so that when you click F15, F16 cells will appear on the month of the drop-down menu.

2014A-QIUHE-1

Next, in order to make the selected range visible in the original data area, you can set the conditional formatting of the original data area, using color to mark the selected area. Selected A2: A11 region, switch to the “Start” tab, click “Conditional Formatting → highlight cell rules → equal to”, in the pop-up “equal to” window “Format cells equal to the following values” at the selection of cell F14, and then set the desired color, so that the cell area in A2:A11 by setting the color to highlight the name displayed in cell F14. selected B1: M11 cell area, click “conditional formatting → New Rule” in the pop-up window, select “use the formula to determine the format of the cell”, in the “value of this formula to set the format “at the input “= AND ($ A2 = $ F $ 14, AND (B $ 1 >= $ F $ 15, B $ 1 <= $ F $ 16))”, set the required format, so that the B1: M11 cell area according to the set starting and ending month to highlight the corresponding sales data (Figure 2).

2014A-QIUHE-2

Finally, in the cell corresponding to “Total” enter the formula “=SUM(OFFSET($A$1,MATCH($F$14,$A$2:$A$11,0),MATCH($F$15,$B$1:$M$1,0),1,INT( SUBSTITUTE($F$16, “month”,””))-INT(SUBSTITUTE($F$15, “month”,””))+1))”, so that you can achieve dynamic summation by month and cumulative summation (Figure 3).

2014A-QIUHE-3

Tip.

SUBSTITUTE function will first be used to replace the cell “month” to empty, and then use the INT function to replace the SUBSTITUTE function to generate the number into a numerical value.

Leave a Comment