Google Sheets 101: Math & Logic

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:

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:

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

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.