## About this lesson

Learn to use SUMIF, COUNTIF and SUMPRODUCT 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

Sample data

- Create a new worksheet

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, a criteria to meet, and a different range to count.
- 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”,B1:B10) will count all records in B1:B10 where the corresponding value in A1:A10 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.