SUM and SUMIF Formulas in Excel

Many readers of this blog have asked for an explanation of the formulas of articles in this blog that use Microsoft Excel Spreadsheet. This way it will be free for them to modify that Excel sheet and make it better suit their needs.

To respond to those requests, now I will explain some formulas that I use in Excel Spreadsheet. SUM and SUMIF are the most often formulas I use.

SUM
SUM formula will add up numbers of all cells that contain number, see examples below.

The formula will add up all the numbers in the table on the left. If viewed in more detail will appear as shown below.

Look at the cell C3, a written formula =SUM(G2:G6), where the formula adds up all numbers in column G from row 2 to row 6. SUM function can be placed anywhere, not necessarily just below numbers that will add up, as if calculating manually. The important thing is to determine which numbers or cells or range that will be added up. Determination of cell can be by column, row, or even random.

Formulas in Excel can be manually typed in the cell. But for a clearer and more detail explanation, it is better to use the Insert Function in the Formula Bar as shown below.

The red arrow is mouse pointer and pressing the fx (Insert Function) on the Formula Bar, to fill the formula in cell C3. After the fx button is presse, then below window will appear.

Select the SUM formula in formula list, shown in blue color and press the OK button. Then it will appear a window like below picture. If the SUM formula is not visible, it can be searched by typing in ‘Search for a function’ window, it appears with black background, then type the Go button. Then the formula will appear in ‘Select a function’ window like above picture.

Select cells or range that will be added up, on the above picutre the range is G2 to G6 (G2:G6), then press Ok, then the formula will be written directly and function in cell C3. If the data are in separated cells or ranges, it can be added into the Number 2, Number 3, etc. up to 255 data. Figure below shows example of separated data. Those data are the same as above but broken down into 3 different tables.

SUMIF
SUMIF formula is used to add up data from multiple cells with specific criteria, not all data will be added up. See the picture below, SUMIF will add up only if data fit a certain criteria. It can be seen that SUMIF adds up ‘apple’ numbers and equal to 4.

If we look in the right table, there is number 1 and number 3 in cell adjacent to apple cells, so the SUMIF of apple will be = 1+3 = 4.

For mango, with a total of 8. If viewed in the right table, at every cell containing mango, adjacent to cell with number 2, 1, and 5. Then the SUMIF of mango will be = 2+1+5 = 8.

Similarly to orange, with a total of 7. If viewed in the right table, the cell containing orange, adjacent to the cell with number 7. Then the SUMIF of orange will also be a 7, because there is only one cell that contain orange.

Figure below shows the detail of the SUMIF formula.

The range in the formula above is column F from row 3 to row 8 where name of fruit are written, in the formula it is written as F3:F8. The criteria used is cell B6, which is apple. Whereas the cells that will be added up are in the column G with row 3 to row 8 or G3:G8. So the formula will read the criteria in cell B6 as apple, then look for cells that contain apple in column F of row 3 to 8, and then summing cells in column G which adjacent to cells which are containing apple.

SUMIF formula can be applied to data that is written horizontally, see picture below. It can be seen fruit names arranged horizontally, with numbers below fruit. On smaller table under the large table, it appears the sum in accordance with the formula SUMIF criteria. Because this horizontal arranged data is exactly the same as above data, which is arranged vertically, then the sum of SUMIF for each criterion is the same, ie 4 for apple, mango 8, and 7 to orange. But horizontal arranged data is rather confusing and not commonly used.

Details of steps to implement the SUMIF formula is as follows. Select the cell that will contain the formula, shown in the figure below is C6. Press the fx (Insert Function) in the formula bar as shown by the red arrow.

Then it will appear a window as below. Select with mouse SUMIF formula, as in picture it has blue background, then press OK. If the formula does not appear on the list then can search by writing on the window ‘Search for a function’ and press Go.

The next display will appear as shown below. With the Range column F from row 3 to row 8 (F3: F8) contain names of fruit. Criteria is the cell B6, the apple, so the formula will calculate apple data only. Sum_range is an area where data is retrieved, it is column G rows 3 to row 8 (G3:G8), and formula will only add up apple data. It can be seen the figure 4 below Sum_range line, this figure to check whether the formula is correct. And apple indeed has data with number 1 and 3, so 1+3 = 4, therefore the formula is correct. So then the OK button can be pressed.

Do the same for the mango and orange. Formula can be copied and paste and then change their parameters.

In the Inventory Control Program, SUMIF formula is used to calculate data in different sheet, as shown below. Can be seen in Formula Bar,  the criteria in SUMIF formula is in ‘register’ sheet, while calculated data in ‘transaction’ sheet. SUMIF is used to calculate the transaction with criteria is a specific part number (P/N).