Similar to the COUNTIF function, you can also add up a range of cells that meets a particular criteria.
Let’s say you’re preparing a budget and want to see how much you’re paying in salaries for each department. Using a list of employees, departments, and salaries, you can use a SUMIF function for one department at a time.
Under the Formulas tab in the ribbon, select Math and Trig, and select SUMIF from the dropdown menu.
The range is the group of cells where Excel will look for the criteria, in this case the department employees work in. The criteria is the value that determines which cells will be added (e.g. sales, finance, administration). The sum range is the range of cells that will be added; we’ll use salaries in our example. If you leave sum range blank, it will default to the range (e.g. if you want to add all salaries that are equal to, or above or below, a certain value).
The sales department has a total of $248,000 of salaries.
The criteria can be text, a number, a date, a cell reference, or a logical expression (e.g. add, subtract, multiply, divide).
If the criteria is text, you can put a wildcard (*) at the beginning or end to sum anything that starts or ends with certain text.
There are a couple options to count cells that are not blank. The criteria “*” will count anything with text. Cells with numbers or text will be ignored and treated as blank. The criteria “<>”&” “ will count everything – text, numbers, and dates.
To count cells that are blank, the criteria “<>”&”*” will count cells not containing any text, and “ “ will count cells not containing any data – text, numbers, or dates.
If you use a cell reference, e.g. F2, as a criteria, SUMIF will look for whatever value is in that cell, and you can change it to get different results, e.g. if you wanted to count employees in the sales department, then count those in the finance department, then those in the administration department, etc.
When using a number or date as a criteria, you can use the specific number/date, as well as:
- Less than: <
- Greater than: >
- Not equal to: <>
- Less than or equal to: <=
- Greater than or equal to: >=
- Any of these, in conjunction with arithmetic, e.g. greater than or equal to a date plus 5 days.