Locked lesson.
About this lesson
Learn to use SUMIF and COUNTIF to add cells only when certain conditions are met.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Conditional Math.xlsx27.6 KB Conditional Math - Completed.xlsx
30.7 KB
Quick reference
Conditional Math
Using SUMIF and COUNTIF 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, 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
- Allows provision of a multiple ranges of values to examine, a criteria to meet, and a different range to SUM
- All tests must be true in order to have the value added in
- =SUMIFS(B1:B10,A1:A10,”>=2”,A1:A10,”<=5”,) will sum all records in B1:B10 where the corresponding value in A1:A10 is greater than or equal to 2 AND less than or equal to 5.
- SUMIFS did not exist in Excel 2003 and earlier. To achieve multiple condition sums in Excel 2003 and prior, you must use the SUMPRODUCT function.
Lesson notes are only available for subscribers.