Microsoft Excel is commonly known for its ability to perform various functions in Excel Sheet. It allows the user to calculate the total amount of more than one items mentioned in a list, vertically or horizontally. If you are wondering that how to make the best use of the Sum If Begins With function—study the following short tutorial to find out:
Calculating an amount using Sum If Begins With function:
Figure 1. Example of calculation total amount using the SUMIF function
When preparing extensive lists on Excel Spreadsheet, a user is often required to sort out a specific set of items for adding up the costs of the items. Or, it might be inclined towards calculating the values assigned to a similar set of items. For instance, the example given above illustrates the summation of similar items mentioned in Column C. Also, you can see the formula applied to cell G7 for estimating the total amount of the amount of the specified item (mince).
The formula uses SUMIF function, which is primitively used for summing up the total numbers which meet the limitation or criteria in a cell row-wise or column-wise. The SUMIF function is used for adding up the cells that contain a numeric value, text, value, dates, emails, etc.
The SUMIF function comprises of wildcards and certain logical operators, which helps a user in summing up the values of a selected group of items collectively. The function focuses on the sum_range for adding up the values given in the cells.
sum_range refers to the cell, whose valued are combined together. If a user is more likely to eradicate the sum_range from the formula then, it results in summing up the range instead.
How does the formula work?
SUMIF is one of the most used formulae on Excel, and it could be modified by the addition of specific asterisks to it. For instance, have a look at the following example:
Figure 2. Example of using SUMIF formula for finding the total cost of mince
As you can see, the formula scans through the list of the items and its corresponding amount for adding up the specified item in F6 (mince). A user is manually required to insert the word “mince” into the formula for finding out the total costs of the items mentioned in C5 and C6 respectively. Next, a user should track Column D, and it should add D5:D11 to the formula, which would calculate the total amount of the desired item. Once the user presses ENTER, the cell would illustrate the total amount in G7.
We used the following formula in G7 for obtaining our desired amount for the selected items:
The SUMIF function should be supplemented further with wildcards or asterisks for enabling its task. An asterisk (*) in the formula signifies that the cell has more than one value for calculation. On the contrary, a question mark (?) represents a single character in the cells.
The inclusion of the wildcards to the equation enables a user to create variations of the SUMIF function. However, a user should be mindful of using asterisks or wildcards when alternating the values in a formula bar to avoid the occurrence of any errors. Also, when typing the formula into cell or formula bar, a user should always enclose the wildcards with (“”).
Similarly, the formula can be alternated and implemented to other items given in a column or a row. For instance:
Figure 3. Example of using SUMIF formula for finding the total cost of the hoodie