Suppose the report is shown in figure 1, and the total score, ranking and total score of each subject need to be calculated automatically.

1. Enter the formula in F3: = b3+d3; Enter and drop down to fill in column F in the table.

See figure 1.

2. Enter the formula in cell C3: = if (B3).

See figure 2.

3. right-click C3 cell > copy

See figure 3.

4. select columns c, e and g in the table about ranking, and right click > paste special > select: paste formula icon;

See figure 4.

5, the actual effect

See figure V.