LESSONS 1. Intro 2. Cell Referencing 3. Data Types 4. Operators 5. Functions 6. Intro To Logic 7. Simple Math 8. Math & Logic 9. Changing Text 10. Replacing Text 11. Splitting Text 12. Joining Text 13. Sorting Data 14. Rearranging Data 15. Looking Up Data

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

=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.
 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
Next Lesson  FREE RESOURCE

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