This is an overview.
The SUMIF Function in Excel
The SUMIF function sums a given range based on one condition only. It adds the values if the condition matches the given value.
The syntax of the SUMIF function is:
=SUMIF(range,criteria,[sum_range])|
ARGUMENTS |
REQUIRED/OPTIONAL |
VALUE |
|
range |
Yes | The range of cells that you want to search based on a condition. It must include numbers or names, arrays, or references that have numbers. Blank and text values are ignored. |
| criteria | Yes | The criteria are a number, expression, cell reference, text, or a function that defines the cells to be added. |
| sum_range | Optional | To add cells that are not specified in the range argument. |
Example: Use the SUMIF Function to Get the Summation Based on Single Criteria
The following dataset contains Salespersons’ names, their selling Products, and Total Sales.
To find John’s Total Sales:
Steps:
- Select a cell to see the Total. Here, C17.
- In C17, enter the following formula.
=SUMIF(C5:C12,C15,D5:D12)
- Press Enter.
Formula Breakdown
- C5:C12 is selected as range. It refers to the Salesperson column.
- C15 is the criteria (John).
- D5:D12 is the sum_range. It refers to the Total Sales column.
- The formula returns the summation of values in the sum_range that match the criteria.
Read More: SUMIF between Two Values in Excel
The Excel SUMIFS Function
The SUMIFS function sums cells based on multiple criteria. It can sum values of criteria based on dates, numbers, and text. The logical operators (>,<,<>,=) are used to match conditions and wildcards (*,?) for a partial matching.
The syntax of the SUMIFS function is:
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2],[criteria2],…)| ARGUMENTS | REQUIRED/OPTIONAL | VALUE |
| sum_range | Yes | Range of cells to sum based on criteria. |
| Criteria_range1 | Yes | Range of cells to apply the criteria. |
| Criteria1 | Yes | Condition for the criteria_range1. |
| Criteria_range2, criteria2, … | Optional | Additional ranges that denote criteria. (127 range/criteria pairs can be used). |
Example: Apply the SUMIFS Function to Get the Summation Based on Multiple Criteria in Excel
The dataset below contains 4 columns: Invoice No, Salesperson, Product, and Sales.
To calculate Jimmy’s Total Sales of TVs:
Steps:
- Select a cell to calculate the Total Sales.
- Enter the following formula:
=SUMIFS(E5:E13,C5:C13,C16,D5:D13,C17)
- Press Enter to get the Total Sales.
Formula Breakdown
- E5:E13 is selected as sum_range.
- C5:C13 is criteria_range1 and C16 is criteria1.
- D5:D13 is criteria_range2 and C17 is criteria2.
- The formula returns the summation of values in the sum_range that match both criteria.
SUMIF vs SUMIFS
If you have a single criterion, you can also use the SUMIFS function.
The following dataset showcases Projects, their Start Date, End Date, Rate Per Hour, and Total Bill.
To find the Total Bill of projects completed before December 21:
Steps:
- Select the cell to see the Total Bill. Here, C15.
- In C15, enter the following formula.
=SUMIF(D5:D10,C13,G5:G10)
- Press Enter.
You can also solve this problem using the SUMIFS function:
Steps:
- Select the cell to see the Total Bill. Here, C15.
- In C15, enter the following formula.
=SUMIFS(G5:G10,D5:D10,C13)
- Press Enter.
SUMIF vs SUMIFS: Which Excel Function to Choose?
The SUMIFS and the SUMIF functions return a similar output, but the SUMIFS function can handle multiple criteria.
To find the total bill of projects completed before December 21 with working hours less than 200:
Steps:
- Select the cell to see the Total Bill.
- Enter the following formula.
=SUMIFS(G5:G10,D5:D10,C13,F5:F10,C14)
- Press Enter.
Download Practice Workbook
Download the practice workbook.
Related Article
<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
