|2. Cell Referencing|
|3. Data Types|
|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:
|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.|
All of these functions test a range against a criterion:
- COUNTIF(range, criterion)
- SUMIF(range, criterion, [sum_range])
- AVERAGEIF(range, criterion, [average_range])
- 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.
Here’s some sales data:
You need to find out:
1. How many sales Jim made
2. Dwight’s total sales
3. Average sales that are less than $1,500
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.