In this tutorial, we will cover how to use the SUMIF function with different types of criteria, including numeric, text, and date criteria. We will also demonstrate how to use the SUMIF function with AND/OR criteria to sum values based on multiple conditions.
Step-by-step Guide to Use SUMIF Function in Excel
- Open your Excel spreadsheet and select the cell where you want the sum to appear.
- Type =SUMIF( in the cell you selected.
- Select the range of cells that you want to apply the criteria to.
- Type a comma, then type the criteria that you want to apply. This can be a number, text, or a logical expression.
- Type a comma, then select the range of cells that you want to sum.
- Type a closing parenthesis “)” and press Enter.
Examples
Numerical Criteria
Summing values using a number criteria. In the above example, we have a list of numbers, and we want to sum up the numbers that are greater than or equal to 4. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(C2:C9, “>=4”) and press Enter. This tells Excel to add up all the numbers of cells in the range C2:C9 that are greater than or equal to 4.
Adding multiple criteria. In the same example, we want to add up the numbers “Product Code 3” in Office 1 and “Product Code 6” in Office 2. To do this, we can use the SUMIF and OR functions. We begin by entering the formula =SUMIF(A2:A9,3,B2:B9)+SUMIF(A2:A9,6,C2:C9) and pressing Enter. This tells Excel to add up all the numbers of cells in the range B2:B9 that have a product code of 3, and the numbers of cells in the range C2:C9 that have a product code of 6.
Text Criteria
Summing values using a text criteria. In the following example, we have a list of products in column E and the amount of sale in column F, and we want to add the number of sales that are by “Brit Biscuits.” To do this, we can use the SUMIF function. First, we input the formula =SUMIF(E2:E9, “Brit Biscuits”, F2:F9) and press Enter. This tells Excel to add all values in F2:F9 that are by Brit Biscuits.
Summing values excluding an item. Using the same example, we want to add up all the sales of all products EXCEPT for “Brit Biscuits.” To do this, we can take the same formula from before and simply add a non-equal-to (<>) operator before the criteria and press enter. The formula should look like this: =SUMIF(E2:E9, “<>Brit Biscuits”, F2:F9). This tells Excel to add all values in F2:F9 that are NOT by Brit Biscuits.
Summing values that start with the same text. In the same example, we have different products that have “chips” and “biscuits” in their names. We want to find out how many Chips2 and Chips3 have sold. To do this, since we know that both products start with the word “Chips,” and only have one character after them, we can add a question mark (?) after the word. The question mark indicates a wildcard character for an individual character, which in this case is the 2 and 3.
The formula should be =SUMIF(E2:E9, “Chips? “, F2:F9). This tells Excel to add all values in F2:F9 that start with “chips.”
Summing values that end with the same text.Similarly, we want to find out how many Lays Chips and Uncle Chips have sold. To do this, since we know that both products contain “Chips” in their names, we can use that to set the criteria. In the formula, simply add an asterisk (*) before the “chips.” The asterisk indicates a wildcard character for any character, which in this case is “Lays” and “Chips.”
The formula should be: =SUMIF(E2:E9, “*Chips “, F2:F9). This tells Excel to add all values in F2:F9 that end with “chips.”
Adding multiple criteria. In the same example, we want to add up the sales of Chips2, Chip3, Biscuits2, and Biscuits3. To do this, we can use the SUMIF and OR functions, while also using the question mark. We begin by entering the formula =SUMIF(E2:E9, “Chips? “, F2:F9)+SUMIF(E2:E9, “Biscuits? “, F2:F9) and pressing Enter. This tells Excel to add all values in F2:F9 that start with “biscuits” and “chips.”
Date Criteria
Summing values using a date criteria. In the following example, we have dates of transactions from April 1, 2021 to April 7, 2021 in column H and the amount in column I, and we want to add the number of transactions after April 3. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(H2:H8, “>”&DATE(2021,04,3), I2:I8) and press Enter. This tells Excel to add all values in I2:I8 after the 3rd of April.
Summing values using today’s date as a criteria. Using the same example, let’s say today’s date is April 7, 2021, and we want to add the number of transactions after today. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(H2:H11, “>”&TODAY(), I2:I11) and press Enter. This tells Excel to add all values in I2:I11 after today.
Summing values between dates. Using the same example, we want to add the number of transactions from April 3 to April 6. To do this, we can use the SUMIFs function. First, we input the formula =SUMIFS(I2:I11,H2:11,”>”&DATE(2021,04,3),H2:H11,”<”&DATE(2021,04,06)) and press Enter. This tells Excel to add all values in I2:I11 after April 3 and April 6
Multiple Criteria
In the following example, we have the type of product in column H, the company in column I, and the price in column J. We want to find out the total price of chips by the company, Lays. To do this, we use the SUMIFS function. First, we input the formula =SUMIFS(J14:J22,H14:22,”Chips”,I14:I22,”Lays”) and press Enter. This tells Excel to add all values in J14:J22 that are Chips AND also by Lays.
Conclusion
The SUMIFS function in Excel is a powerful tool that allows users to sum cells based on multiple criteria. With the SUMIFS function, users can specify multiple criteria and ranges to sum cells that meet all of the specified criteria. This function is useful for analyzing data and making informed decisions based on multiple conditions. It is important to note that the SUMIFS function requires all criteria to be met in order for the cell to be included in the sum, whereas the SUMIF function only requires that one criteria be met.