Locked lesson.
About this lesson
Learn to use SUMIF, COUNTIF, and SUMIFS to add cells only when certain conditions are met.
Exercise files
Download this lesson’s related exercise files.
Conditional Math - Begin.xlsx28.6 KB Conditional Math - Complete.xlsx.xlsx
26.1 KB
Quick reference
Conditional Math
Using SUMIF, COUNTIF, and SUMPRODUCT to add cells only when certain conditions are met
When to use
Useful when you need to summarize values only in cases where certain criteria are true
Instructions
SUMIF
- Allows provision of a range of values to examine, a criteria to meet, and a different range to sum
- Each row in the existing range is examined against the criteria
- If a condition is met, then the matching row in the “sum range” is added to the total
- Mathematical decisions provided as the criteria should be wrapped with quotes
- =SUMIF(A1:A10,”>5”,B1:B10) will sum all records in B1:B10 where the corresponding value in A1:A10 is greater than 5.
COUNTIF
- Allows provision of a range of values to examine, as well as a criteria to meet in order to be counted
- Each row in the existing range is examined against the criteria
- If a condition is met, then the matching row in the “count range” is added to the total
- Mathematical decisions provided as the criteria should be wrapped with quotes
- =COUNTIF(A1:A10,”>5”) will return a count of all records in A1:A10 where the value is greater than 5.
SUMIFS/COUNTIFS/AVERAGEIFS
- Allows calculations where multiple conditions are met
- Unlike the “IF” versions of the formula, the “IFS” versions require the calculation range first
- After the calculation range, one or more pairs of parameters are provided, where the first is the range to check, and the second is the condition
- In order to be included in the total, ALL of the conditions must be true for any given line
- Keep in mind that all calculation and criteria ranges must have the same number of rows
Lesson notes are only available for subscribers.