Some spreadsheet functions combine math and logic.

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

Spreadsheets have you covered with these functions:

AB
1FunctionReturn
2COUNTIFHow many numbers that meet specified a criterion are present (not text, just numbers).
3SUMIFSum of numbers that meet specified a criterion.
4AVERAGEIFThe average of the numbers that meet specified a criterion.

Syntax

All of these functions test a range against a criterion:

Where:

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:

AB
1Jim$1,807
2Dwight$1,441
3Andy$1,681
4Jim$1,306
5Jim$932
6Dwight$1,786
7Dwight$1,458
8Dwight$1,362
9Andy$1,777
10Jim$1,338

You need to find out:

1. How many sales Jim made

=COUNTIF(A1:A10,"Jim")

4 sales

2. Dwight’s total sales

=SUMIF(A1:A10,"Dwight",B1:B10)

$6,047.00

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

=AVERAGEIF(B1:B10,"<1500")

$1306.17

Math & Logic Exercises
Enter a formula for each question in the formula bar for the spreadsheet below to access the next lesson.
1. SUMIF
In cell B8, enter a formula that gets the sum of sales for salespeople who sold more than $5,000.
2. COUNTIF
In cell B9, enter a formula that counts how many salespeople sold more than $5,000.
3. AVERAGEIF
In cell B10, enter a formula that gets the average of sales for salespeople who sold more than $5,000.
ABC
1FirstLastSales
2DwightSchrute10,000
3JimHalpert8,000
4StanleyHudson7,000
5PhyllisVance6,000
6AndyBernard4,000
7
8SUMIF
9COUNTIF
10AVERAGEIF
11
Next Lesson

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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.