Saturday, October 16, 2021
HomeTipsOfficeSUMIF function in Excel: Usage and specific examples

SUMIF function in Excel: Usage and specific examples

What is the SUMIF function?

SUMIF is a function used to sum values ​​that satisfy certain conditions, first introduced in Excel 2007. The SUMIF function can be used to sum cells based on dates, numbers, and matching text. case provided. This function supports logical operations (>, , =) and notation (*,?) for partial matching.

You must be wondering, is the SUMIF function different from the SUM function? The SUMIF function extends the ability of the SUM function, instead of summing within a certain range, to sum in SUMIF cells that must satisfy the conditions that the user passed in the criteria parameter. The SUMIF function saves a lot of work if you want to calculate the total revenue of a unit, the sales of a group of employees, or the revenue for a certain period of time, the total salary under certain conditions…

SUMIF function in Excel 2016

The purpose of the SUMIF function is to sum numbers in the range that meet the criteria provided. The returned result is the sum of the provided values.

SUMIF . function formula

=SUMIF (range, criteria, [sum_range])

SUMIF function parameters:

  • Range: The range of cells you want to evaluate against the Criteria criteria. The cells in 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 the standard Excel format.
  • Criteria: Criteria to determine the values ​​to be summed. It can be a number, an expression, or a text string.
  • Sum_range: This parameter is optional, it will indicate the cells to be summed. If left blank sum_range, cells in the evaluation range will be replaced.

Example and how to use SUMIF in Excel

Let’s learn with Quantrimang.com how to use the SUMIF function. Suppose we have a summary of income and personal income tax as follows:

total income Number of dependents Income taxes PIT Real income received Department
80,000,000 2 63,348,500 13,154,550 66,393,950 Accountant
30,000,000 won first 16,948,500 1,792,275 27,756,225 Sell
20,000,000 won first 6,875,000 won 437,500 19,037,500 Sell
10,000,000 won first -3,650,000 won 0 8,950,000 won Sell
100,000,000 2 82,750,000 won 19,112,500 79,837,500 Accountant
1,000,000,000 first 976,900,000 won 332,065,000 won 657,435,000 won Manager

In Excel the table is presented as follows:

Example of SUMIF function in Excel

Example 1: Use the SUMIF function to calculate the total personal income tax of people with incomes below VND 50 million.

To solve this example, we first need to determine the 3 parameters of the SUMIF function, specifically:

  • Range: Column range containing total income, here is B4:B9
  • Criteria is
  • Sum_range is the column range containing personal income tax to sum, here is H4:H9

Then we will have the formula: =SUMIF(B4:B9, “H4:H9), you enter this formula in the cell containing the result, which in our example is cell H19.

Determining the parameters of the SUMIF function in Excel

Press Enter and will receive the amount of personal income tax due for those earning less than 50,000,000,000 is 2,229,775.

The result of the SUMIF . function

Example 2: Still with the above spreadsheet, calculate the total personal income tax of people with 2 dependents. Right now:

  • Range: Column range containing the number of dependents, here D4:D9
  • Criteria is =2, can be written as 2 or “=2”
  • Sum_range is the column range containing the personal income tax to be summed, here is H4:H9.

Thus, the formula would be: =SUMIF(D4:D9,2,H4:H9), you enter the formula in the cell containing the result, in the example H21:

Use the SUMIF function to calculate total personal income tax

Press Enter to receive a total tax return of 32,267,050 VND.

Result returned when calculating total tax by SUMIF

Example 3: Still in the table above, calculate the total income of people with incomes over 80 million VND. Right now:

  • Range: Column range containing total income, here is B4:B9
  • Criteria is greater than 80 million, written as “>80000000”
  • Sum_range in this case is Range, so you don’t need to enter this parameter anymore.

So the formula would be: =SUMIF(B4:B9,”>80000000″), you enter the box containing the result, here is H22:

Calculating the total income of people with income over 80 million VND

Press Enter to get the result that the total income of people with income over 80 million is 1,100,000,000:

Result of total income of people with income greater than 80 million

Example 4: Still using the original data table, let’s calculate the total income of the sales department. Then:

  • Range: The column range containing the room list here is J4:J9
  • Criteria is Sales, written as “Sales”
  • Sum_range is the column range containing the income to be summed, here is B4:B9

So the formula would be: =SUMIF(J4:J9,”Sell”,B4:B9), you enter the formula in the cell containing the result, as shown below is cell H23:

The formula for calculating the total income of the sales department

Press Enter and receive the total income of the sales department is 60,000,000:

The result of the total income of the sales department when using SUMIF

Attention when using the SUMIF . function

  • When sum_range omitted, the sum will be calculated in Range.
  • Criteria containing letters or mathematical symbols must be enclosed in double quotes “”.
  • The range of numeric specifiers that can be provided is numeric without the use of brackets.
  • 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 an actual question mark or asterisk, type a tilde (~) before the character.

Above is how to use the basic SUMIF function in Excel, it’s actually very easy, isn’t it? You just need to specify the correct column range that needs to satisfy the criteria, the criteria and the column range to be summed. If you need to sum by multiple conditions, then remember to use the SUMIFS function, much easier and faster.

If you have any questions in the process of manipulating the SUMIF function, please comment below for help.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -

Most Popular

Recent Comments