## About this lesson

Learn to use SUMIF, COUNTIF and SUMPRODUCT to add cells only when certain conditions are met.

## Exercise files

Download this lesson’s related exercise files.

Conditional Math.xlsx23 KB Conditional Math.xlsx

23 KB

## Quick reference

### Topic

Conditional math.

### Description

Using SUMIF, COUNTIF and SUMPRODUCT to add cells only when certain conditions are met.

### Where/when to use the technique

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, 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/COUNTIFS

- Allows summing or counting where multiple conditions are met
- =SUMIFS(D1:D10,A1:A10>,”>5”,B1:B10,10)
- The first range listed (D1:D10) is the range that will be summed
- Each row in A1:A10 is evaluated to see if it is greater than 5.
- Each row in B1:B10 is evaluated to see if it equals 10.
- If the record in column A is >5 AND the record in column B = 10, then the value from column D will be included in the SUM total

Lesson notes are only available for subscribers.