About this lesson
In this lesson, we learn the basic uses of SUMIF() and COUNTIF().
Download this lesson’s exercise file.Conditional Math.xlsx
Conditional Math Functions
Learn the basic uses of SUMIF() and COUNTIF()
When to use
These functions are useful to add or count a range of values based on a certain criterion
- SUMIF() syntax: =SUMIF(range, criteria, [sum range])
- Range: the range Excel will use to compare the criteria to
- Criteria: the criteria that a value in the range must meet to satisfy and be included in the sum
- Sum range: optional – if you omit this, Excel will perform the sum on the range specified earlier
- To add the total sales per location, the location column is the range, the cell where the location is specified is the criteria and the sum range is the Total sale column.
- COUNTIF() syntax: = COUNTIF(range, criteria)
- Range: the range that Excel will use to compare to the criteria
- Criteria: the criterial that a value in the range must meet to satisfy and be included in the count
- To count the number of transactions per location, the range is the location column, the criteria is the cell containing the location
- In both the first examples, text was used as the logic test, the next example uses a number in the logic test
- SUMIF() adds the units sold where the total sale exceeds a specified value ($500). The logic symbol is included in the formula in quotes and combined with the amount using an &. (“>=”&cell reference($500))
Lesson notes are only available for subscribers.