The excel SUMIF function: YOUR OBJECTIVE
You have a list of your company’s international sales. Analyzing the data in that table, you want to find out how much business was done in individual countries. Ideally, you would like to type in a country’s name and have Excel display the total dollar amount of sales made in that country. Excel’s SUMIF function is suitable for this purpose. It adds together values selectively, adding together only those items in a cell range that meet a specific criterion.
In this learning module “ The excel SUMIF function”, you will be shown how to use the SUMIF function.
HOW DOES THE EXCEL SUMIF FUNCTION WORK?
The sales numbers that are to be (selectively) summed up in the are in column D of your sales table; the countries are in column E. You want to be able to type a country name into cell H2 and have the total sales for that country automatically appear in H3. You will accomplish this by inserting a excel SUMIF function in cell H3.
To locate the excel SUMIF function, select the Math & Trig category in the Or select a category: selection list. Then, from among those functions listed in the Select a function: box, find and select excel SUMIF. Now go on to the next step by clicking OK.
As you can see in the open Function Arguments dialog box, the excel SUMIF function requires three arguments:
The Range argument identifies the range of cells in which your search criterion (in this case, the country) will be sought, in the current example: E2:E2156.
Here you should enter the cell address of the cell in which you will later type a country name, in this example: H2.
Sum_Range is the range of cells that contains the numbers your want to (selectively) add together. In the current example, this should indicate the cell range of the values in column D, i.e., the cell range D2:D2156.
If you don’t want to vary the search criterion, you wouldn’t have to enter a cell reference to point to the search criterion in your table, you could it directly into the Criteria input box in the Function Arguments dialog box. If you enter a sequence of characters (text), Excel will automatically put it in quotation marks. You can also enter a specific number or a comparison (e.g., <5).
If you want to identify a large cell range using just the mouse, you would soon discover that it is difficult to control the mouse pointer over so many cells (in the current example, more than 2000). To select a long column of cells, you can proceed as follows: Click the first cell of the range you want to select. Then press and hold [Ctrl]+[Shift]. Next, press the [Down-arrow] key. This causes all populated cells (cells with content) to be selected, until the first empty sell is reached. You can proceed in a similar fashion to select cell ranges to the right, left or upwards.
After filling in the arguments in the Function Arguments dialog box, click OK. If you now enter USA in H2, the total sales for the USA will immediately appear in H3.
If you click on H3, the complete formula will appear in the formula bar:
You can read this formula as follows: Search within the entire range E2:E2156 for the value currently contained in cell H2. Wherever a match is found, go to column D (cell range D2:D2156) of the same row and cumulatively add the corresponding value there. Display the final cumulative sum in H3.