How to summarize data by month in Excel

The figures shown in the following figure require a summary of the number of recruits by month. For such aggregate requirements, it is still common in actual work.

Generally we are accustomed to solving problems with formulas, but as a matter of fact, one is more data, there are nearly 1800 data.

Second, the title requirements can be summed up in different years. If you use a formula, you need to first extract the year that does not repeat, and then establish the data validity, and then use the SUMIFS function or the Sumproduct function to complete, not only a large number of operations, but also more steps.

If you use the PivotTable report to complete, it will be relatively convenient, and then we will learn the specific methods of operation.

First, the establishment of a PivotTable report

Click any cell in the data range, and then click Insert, Pivot table.

When you leave the default setting in the Create PivotTable dialog box that pops up, click OK, and Excel automatically creates a worksheet named Sheet1 to place the new PivotTable report.

Second, set up PivotTable fields

In the PivotTable Field List, drag the Entry Date field to the row labels area and the ∑ values area, respectively.

Right-click the PivotTable row field, and in the Drop-down list, click Create Group.

In the Step text box of the Group dialog box, click Month, then year, and then click OK.

In the PivotTable Field List, drag the year of the row label area to the report filter area.

Close the PivotTable Field List.