MS Excel, Autosum and SubTotal Formulas

 

We had described in previous article regarding creating MS Excel workbook and inserting data in it. Now we will try to illustrate summing up the data in excel sheet and other formulas of Microsoft Excel. 

In previous article you had read about inserting data of students of a school. However, there is requirement to get totals of these data to carryout analysis of students studying in school, so we shall get totals of fees amount being deposited by these students. Formula for summing of fees, counting students and class wise students will be as under:-

Primary Keys or buttons

Following are primary keys and buttons used to set formulas in MS Excel:-

Plus    =          Numerical key +, or shift +

Minus =          numerical key -, or shift ‑

Multiply =       Numerical k

ey * or shift 8

Divide =          numerical key / or Oblique key

Ranging between two cells           =          : (Colon)

Overall summing of Fees amount (Sum Formula)

we have fill students for 120 students of different class and supposed equal fees for each class as under:-

Class	Total Students	Fees	Amount	Sum Amount
6th	26	1500	3900 (26 * 1500)
7th	20	1800	36000 (20 * 1800)
8th	22	2100	46200 (22 * 2100)
9th	24	2500	60000 (24 * 2500)
10th	28	2800	78400 (28*2800)
Total	120		259600

Data for these 120 students have reached till row no 121 and, which means that we have to sum the amount of Monthly Fees Columns at cell (H122). Since, place cursor on cell H122 and fill formula with following methods:-

Method 1

Type (=sum (H2:H121)). This means that get sum ranging from cell H2 to H121. This formula will get result as 259600.

Method 2

Place cursor on cell H122 and press AutoSum icon button shown on home menu in right corner or press (Alt + =), which will sum the figures from H2 to H121 in default and formula will be set shown in method 1. 

Sum formula for standard totals is feasible, however we shall suggest that Sum formula should not be set for the recurring expenditure sheets and it should be replaced with subtotals formulas that account can get out desired results during apply filters and sorts on the worksheets. We shall write about filter and sort functions in succeeding articles. Following is procedures for applying subtotal formula:-

Sub Total Formula

Aim of subtotal formula is get output of lengthy worksheet or database having many groups and categories, which helps to get sums of multiple categories. SUBTOTAL formula is divided into many other functions with its prescribed codes as under to get result as per desires. Details of these functions are as under:-

Function number	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

SUBTOTAL Formula Method

As Already mentioned that there are multiple functions to apply Subtotal formula, however, we are only applying now summing up the total fees amount of student applying subtotal formula. So, Place cursor in cell H122 and type formula =SUBTOTAL (9,H2:H121) and press enter. Desired result as equal to sum formula will come.

After starting bracket we type 9, which means that we have to sum and after giving comma, we have typed H2:H121, which means that we have given range for taking sum of our figures or we have given our prescribed range. Also see above table in which all functions of subtotal formula have been given.