Some spreadsheet functions combine math and logic.

These functions allow you to make calculations on specific data that meet provided criteria.

- Want to sum the cost of all items over a certain price?
- Need to count how many sales a particular salesperson has made?
- Have to find the average score of only one class on a test?

Spreadsheets have you covered with these functions:

A | B | |

1 | Function | Return |

2 | COUNTIF | How many numbers that meet specified a criterion are present (not text, just numbers). |

3 | SUMIF | Sum of numbers that meet specified a criterion. |

4 | AVERAGEIF | The average of the numbers that meet specified a criterion. |

## Syntax

All of these functions test a range against a criterion:

- COUNTIF(range, criterion)
- SUMIF(range, criterion, [sum_range])
- AVERAGEIF(range, criterion, [average_range])

Where:

- range = the range reference or array that is tested against criterion
- criterion = a logic test applied to range:
- Equal to: 1 or "1" or "=1" or "text" or "=text"
- Not equal to: "<>1" or "<>text"
- Greater than: ">1"
- Greater than or equal to: ">=1"
- Less than: "<1"
- Less than or equal to: "<=1"

When the criterion tests text data you can include the wildcards ? (to match any single character), or * (to match zero or more adjacent characters). To include an actual ? or * in your text by putting the tilde (~) before the ? or *.

You’ll notice that SUMIF and AVERAGEIF have an additional optional argument.

This optional argument allows you to get the sum or average of a different range to the one being tested against the criterion. If left blank, the provided range is used.

## Example

Here’s some sales data:

A | B | |

1 | Jim | $1,807 |

2 | Dwight | $1,441 |

3 | Andy | $1,681 |

4 | Jim | $1,306 |

5 | Jim | $932 |

6 | Dwight | $1,786 |

7 | Dwight | $1,458 |

8 | Dwight | $1,362 |

9 | Andy | $1,777 |

10 | Jim | $1,338 |

You need to find out:

**1. How many sales Jim made**

4 sales

**2. Dwight’s total sales**

$6,047.00

**3. Average sales that are less than $1,500**

$1306.17

A | B | C | |

1 | First | Last | Sales |

2 | Dwight | Schrute | 10,000 |

3 | Jim | Halpert | 8,000 |

4 | Stanley | Hudson | 7,000 |

5 | Phyllis | Vance | 6,000 |

6 | Andy | Bernard | 4,000 |

7 | |||

8 | SUMIF | ||

9 | COUNTIF | ||

10 | AVERAGEIF | ||

11 |

FREE RESOURCE

### Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

*By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.*