10 Google Sheets formulas beginners can copy

A formula can remove repeated counting, but the wrong range can give a plausible wrong answer. These ten examples follow Google's Sheets documentation and use a small fictional stock list.

The screenshots show Google's public help examples. The answers below are expected results for our sample data.

First open a blank Google Sheet. Enter these rows in columns A, B and C, starting at A1:

1. A1: Item. B1: Quantity. C1: Category.
2. A2: Pens. B2: 12. C2: Stationery.
3. A3: Notebooks. B3: 8. C3: Stationery.
4. A4: Folders. B4: 10. C4: Storage.

For each example, click an empty cell outside that table, paste the formula including =, then press Enter. B2:B4 means all three cells from B2 through B4.

1. Add quantities with SUM

1. Enter =SUM(B2:B4).
2. Compare the answer with 12 + 8 + 10. Expect 30.

Enlarge image

Google's public SUM example showing monthly expenses, totals and SUM formulas

×

Open full-size image

Google's screenshot uses monthly expenses, so its numbers differ from our stock list.

2. Find the average with AVERAGE

1. Enter =AVERAGE(B2:B4).
2. Expect 10, the sum divided by three numeric entries.

Text is ignored. A zero is a number, so it affects the average.

3. Find the smallest quantity with MIN

1. Enter =MIN(B2:B4).
2. Expect 8.

4. Find the largest quantity with MAX

1. Enter =MAX(B2:B4).
2. Expect 12.

5. Count numeric entries with COUNT

1. Enter =COUNT(B2:B4).
2. Expect 3. This counts the numeric entries. Use SUM to add their values.

6. Count filled entries with COUNTA

1. Enter =COUNTA(A2:A4).
2. Expect 3, because all three item cells contain text.

COUNTA also counts cells containing only spaces or empty text returned by a formula. It does not check whether an entry is useful.

7. Count a category with COUNTIF

1. Enter =COUNTIF(C2:C4, "Stationery").
2. Expect 2. Keep the category text inside quotation marks.

8. Add one category with SUMIF

1. Enter =SUMIF(C2:C4, "Stationery", B2:B4).
2. Expect 20, the quantities for Pens and Notebooks.

The first range checks categories. The last range supplies quantities. Keep their rows aligned.

Enlarge image

Google's SUMIF help showing sample usage and the range, criterion and optional sum range syntax

×

Open full-size image

9. Choose a label with IF

1. Enter =IF(B2<10, "Restock", "Enough").
2. Expect Enough, because B2 is 12.

This is a fictional rule. Replace the condition with the rule your task actually needs.

10. Remove extra spaces with TRIM

1. Enter =TRIM(" Demo data "), keeping the spaces inside the quotes.
2. Expect Demo data, with one space between the words.

TRIM does not remove non-breaking spaces.

If the answer looks wrong

Check the starting =, quotation marks and highlighted range. Keep formulas out of their own input range. These examples use English function names and commas.

If a pasted formula shows Formula parse error, compare its argument separators with the function help in your sheet. If it expects semicolons, use =COUNTIF(C2:C4; "Stationery") for the category example. Expect 2 with the supplied stock list. Change only separators between arguments; keep text inside quotation marks unchanged.

On a computer, open File > Settings to check Locale. Changing it affects the entire spreadsheet's default currency, date and number formats for everyone using the file. Google's locale and function-language settings (opens in a new tab) also explain Always use English function names. Keep that option on, where available, to use the English names above.

Sources: Google's formula entry instructions (opens in a new tab) and function reference (opens in a new tab), including COUNTA (opens in a new tab), SUMIF (opens in a new tab) and TRIM (opens in a new tab). Screenshots ©Google, cropped from its linked public help pages.