The SUMIF function in Excel is very commonly used. Do you know how to use the SUMIF function? If not, please read the instructions below.
The SUMIF function in Excel is very commonly used. Do you know how to use the SUMIF function ? If not, please read the instructions below.
Excel is a powerful tool when it comes to data management, and one of its most useful functions is SUMIF. Whether you're a student tracking grades, a small business owner monitoring sales, or simply trying to manage your personal budget, understanding how to use SUMIF can make your life much easier.
This article will cover everything you need to know about using the SUMIF function in Excel. From the basics to more advanced practical applications of the SUMIF function, the article will break it down into sections for your convenience.
Let's start with the basics. The SUMIF function in Excel is used to add values that meet a specific criterion. It's like having a personal assistant that only adds up the numbers you're interested in. Imagine you're managing a small retail store. You have a spreadsheet with sales data and want to find out how much revenue you've generated from a particular product. Instead of manually adding up each sale, SUMIF will do the heavy lifting for you.
What is the SUMIF function?
SUMIF is a function used to calculate the sum of values that meet a given condition, first introduced in Excel 2007. The SUMIF function can be used to sum cells based on dates, numbers, and text that match the provided condition. This function supports logical operators (>, <, <>, =) and symbols (*, ?) to suit different contexts.
You might be wondering, what's the difference between the SUM function and the SUM function ? The SUMIF function extends the capabilities of the SUM function. Instead of summing within a specific range, to be summed in SUMIF, the cells must meet the conditions passed in the `criteria` parameter. The SUMIF function saves a lot of effort if you want to calculate the total revenue of a unit, the sales of a group of employees, or revenue over a specific period, total salaries based on certain conditions, etc.
The purpose of the SUMIF function is to calculate the sum of numbers within a given range that meet the specified criteria. The result returned is the sum of the given values.
You can use the SUMIF function to perform financial calculations in many different contexts. Here are some useful ways to use the SUMIF function in Excel.
- Calculate working hours within a specific period.
- Check the bill payment deadline.
- Follow the statistics of your favorite sports team or football team.
- Monitor personal expenses.
- Calculate employee bonuses.
- Track page views for specific items.
SUMIF function formula
=SUMIF (range, criteria, [sum_range])
Parameters of the SUMIF function:
- Range: The range of cells you want to evaluate according to the Criteria. Cells within each range must be numbers or names, arrays, or references containing numbers. Empty values and text values are ignored. The selected range can contain dates in standard Excel format.
- Criteria : The criteria that determine the values to be summed. These can be numbers, expressions, or text strings.
- Sum_range : This parameter is optional; it specifies the cells to sum. If sum_range is left blank , the cells within the evaluation range will be replaced.
The criteria are applied to cells within the range. When cells within the range meet the criteria, the corresponding cells in sum_range are added together.
*Note when using the SUMIF function
The SUMIF function returns the sum of cells in a range that meet a single condition. The first argument is the range to which the criterion applies, the second argument is the criterion, and the last argument is the range containing the values to sum. SUMIF supports logical operators ( >, <, <>, = ) and wildcards ( *, ? ) for partial matching. The criterion can use a value in another cell, as explained below.
SUMIF is one of eight Excel functions that divides logical criteria into two parts (range + criteria). Therefore, the syntax used to construct the criteria is different, and SUMIF requires a range of cells for the range argument; you cannot use an array.
SUMIF only supports a single condition. If you need to apply multiple criteria, use the SUMIFS function. If you need to manipulate the values that appear in the range argument (i.e., extract the year from the date to use in the criteria), see the SUMPRODUCT and/or FILTER functions .
An example of how to use SUMIF in Excel.
Let's learn how to use the SUMIF function with TipsMake.com.com. Suppose we have a summary table of income and personal income tax as follows:
| Total income | Number of dependents | Taxable income | Personal Income Tax | Net income | Department |
| 80,000,000 | 2 | 63,348,500 | 13,154,550 | 66,393,950 | Accountant |
| 30,000,000 | 1 | 16,948,500 | 1,792,275 | 27,756,225 | Sell |
| 20,000,000 | 1 | 6,875,000 | 437,500 | 19,037,500 | Sell |
| 10,000,000 | 1 | -3,650,000 | 0 | 8,950,000 | Sell |
| 100,000,000 | 2 | 82,750,000 | 19,112,500 | 79,837,500 | Accountant |
| 1,000,000,000 | 1 | 976,900,000 | 332,065,000 | 657,435,000 | Manager |
In Excel, the table is presented as follows:
Example 1 : Use the SUMIF function to calculate the total personal income tax for individuals with incomes below 50 million VND.
To solve this example, we first need to identify the three parameters of the SUMIF function, specifically:
- Range : The column range containing the total income, in this case B4:B9
- The criterion is <50,000,000, because it contains mathematical symbols, so it needs to be enclosed in quotation marks: "<50,000,000"
- Sum_range is the column range containing the personal income tax to be summed, in this case H4:H9
Then, we will have the formula: =SUMIF(B4:B9,"<50.000.000",H4:H9) , you enter this formula into the cell containing the result, in our example cell H19.
Press Enter and you will receive the amount of personal income tax payable for those with incomes under 50,000,000, which is 2,229,775.
Example 2: Using the same spreadsheet, calculate the total personal income tax for a person with two dependents. Now:
- Range: The column range containing the number of dependents, in this case D4:D9
- Criteria is =2, which can be written as 2 or "=2".
- Sum_range is the range of columns containing the personal income tax to be summed, in this case H4:H9.
So the formula would be: =SUMIF(D4:D9,2,H4:H9) , you enter the formula into the cell containing the result, in the example H21:
Press Enter to receive the total tax refund of VND 32,267,050.
Example 3: Still in the table above, calculate the total income of those with incomes above 80 million VND. At this point:
- Range: The column range containing the total income, in this case B4:B9
- Criteria is greater than 80 million, written as ">80000000".
- In this case, Sum_range is the same as Range, so you don't need to enter this parameter again.
So the formula will be: =SUMIF(B4:B9,">80000000") , you enter it into the cell containing the result, in this case H22:
Press Enter to get the result: The total income of those earning over 80 million is 1,100,000,000.
Example 4: Using the original data table, let's calculate the total revenue of the sales department. Then:
- Range: The column range containing the room list here is J4:J9
- Criteria means Sales, written as "Sales".
- Sum_range is the column range containing the income to be summed, in this case B4:B9
So the formula would be: =SUMIF(J4:J9,"Sales",B4:B9) , you enter the formula into the cell containing the result, as shown in the image below, cell H23:
Press Enter and receive the total revenue of the sales department, which is 60,000,000:
Be careful when using the SUMIF function.
- When sum_range is omitted, the sum will be calculated based on Range .
- Criteria containing words or mathematical symbols must be enclosed in quotation marks "".
- The range of numbers that can be provided is numbers that will not require parentheses.
- The characters
?and*can both be used in Criteria. A question mark matches any single character; an asterisk matches any string of characters. If you want to find a real question mark or asterisk, type a tilde (~) before the character.
Use Named Range
You can also use a Named Range in the SUMIF function. A Named Range is a descriptive name for a set of cells or ranges of cells in a worksheet. If you're unsure how to set up a Named Range in your spreadsheet, read TipsMake.com's guide on: How to name cells or data ranges in Excel .
For example, if you have created a Named Range called years in your spreadsheet that references cells A2:A6 in Sheet1 (Note that the Named Range is an absolute reference to the cells =Sheet1!$A$2:$A$6in the image below):
This article can use this Named Range in the current example.
This will allow replacing A2:A6 as the first parameter with Named Range years, as follows:
=SUMIF(A2:A6, D2, C2:C6) 'First parameter uses a standard range Result: 218.6 =SUMIF(years, D2, C2:C6) 'First parameter uses a named range called years Result: 218.6Some frequently asked questions
Question : I have several cells in my spreadsheet, but I only need the sum of all the negative cells. So, if there are 8 values, A1 to A8, and only A1, A4, and A6 are negative, then I want B1 to be the sum (A1, A4, A6).
Answer : You can use the SUMIF function to calculate only the sum of negative values, as you described above.
For example:
=SUMIF(A1:A8,"<0")This formula will only sum the values in cells A1:A8 - those cells with negative values (i.e., <0).
Question : In Microsoft Excel, I'm trying to achieve the following using the IF function:
If a value in any cell in column F is "food", then add the value of its corresponding cell in column G (for example, the corresponding cell for F3 is G3). The IF function is executed entirely in a separate cell. I can do that for a pair of cells but don't know how to do it for an entire column.
Currently, I have the following:
=IF(F3="food"; G3; 0)Answer : This formula can be created using the SUMIF function instead of the IF function.
=SUMIF(F1:F10,"=food",G1:G10)This formula will evaluate the first 10 rows of data in the spreadsheet. You may need to adjust the ranges accordingly.
If you separate your parameters with semicolons, then you may need to replace the commas in the formula above with semicolons.
Why isn't the SUMIF function working?
There are several reasons why the SUMIF function in Excel might not work. Sometimes, your formula doesn't return the expected result simply because the data type in the cell or in some of the arguments doesn't match the SUMIF function. Below are some important things to keep in mind.
SUMIF only supports one condition.
The SUMIF function formula only has space for one condition. To sum with multiple criteria, use the SUMIF function (add cells that meet all conditions) or build a SUMIF formula with multiple criteria OR (sum cells that meet any condition).
Range and sum_range should have the same size.
For the SUMIF function to work correctly, the range and sum_range arguments should have the same size; otherwise, you may get inaccurate results. The problem is that Microsoft Excel doesn't rely on the user's ability to provide the correct range, and to avoid potential inconsistencies, it automatically determines the sum range in this way:
Sum_range only specifies the top-left cell of the range to be summed. The rest is determined by the size and shape of the range argument.
Above is a basic explanation of how to use the SUMIF function in Excel. It's actually very easy, isn't it? You just need to identify the correct column range that needs to satisfy the criteria, the criteria, and the column range to sum. If you need to sum based on multiple conditions, remember to use the SUMIFS function ; it's much easier and faster.
If you have any questions while working with the SUMIF function, please leave a comment below for assistance.