Beginner Functions

What are formulas & functions?

What is a function?

A function is a way to calculate a value in Google Sheets. Functions are strings of text like Sum(A2:A3), or If(A1=”Apple”, “True”, “False”). Functions start with a word like “Sum” or “If”. The word before the parenthesis determines what the function will do and the way the values in the parenthesis need to be formatted. 

Functions can be put together to make a formula. 

What is a formula?

According to the Google Docs formulas & functions page, a formula is “ A formula is anything that’s entered into a cell that begins with the equals character (=). For example: =2+3”

What??

If these definitions made your eyes glaze over, don’t worry! I’ll explain some  beginner-friendly functions below that might make these definitions more clear.

What’s the main takeaway?

You can enter functions and formulas into cells in your Google Sheets document that will automatically do calculations for you! All functions start with an equal sign, then a word that defines the function and tells the computer what to do (in green below), then you’ll add specific criteria in the parentheses (in blue below). Here’s an example:

= Sum(A1,A2)

Remember to start each formula with an equal sign. That’s what tells the computer that it needs to do a calculation. Let’s say that cell A1 = 2 and cell A2 = 3 in the above example. If you typed the above formula into a cell and hit enter, the cell with the formula would take the sum of cell A1 (2) and cell A2 (3) and return a 5. However, if you didn’t put the equals sign in front, then the cell would say “Sum(A1,A2)” which is probably not helpful.

Additional Resources

For more information, Google Sheets has these helpful resources for adding formulas & functions.

Adding cells together

This is a helpful way to combine data. In the example below, I want to know how many people attended one of the drop-in events hosted over the weekend. 

1. Click on the cell where you want to add your formula. This is the place where you want the result of the formula to show up when you’re done. In this case, I clicked cell B6.

2. Start by typing an equals sign then click on the first cell you want to add (B2 in this example), type +, click the next cell you want to add, and continue alternating between clicking cells and typing + until you have selected all of the data you want to add (B2, B4… in this example). Be sure to end by clicking a cell and not typing a + or the computer will look for something else to add. 

Note: You can type the cell name (B2) instead of clicking the cells if you want.

After following step 2, my formula looks like this:

=B2+B3+B4

In the screenshot below, notice how the color of each cell name in the formula is the same as the color of the border of the cell. This is only visible while you’re editing the formula and is a helpful way to see what you have selected in the spreadsheet.

3. Hit enter on your keyboard to make the computer do the calculation. Notice how the cell shows the answer, and the formula bar on top still shows the formula.

4. You’ve now added the contents of the cells together! This exact process will also work for subtraction, multiplication, and division. Just replace the plus sign with - for subtraction, * for multiplication, or / for division. 

If function

The If function is a great way to introduce logical conditions. It is a versatile function that you can use on its own and as part of other formulas. In the example below, I’m planning a party and have a budget of $1,000. I’ve listed out all of my predicted expenses and have them added up at the bottom of the list with the sum function covered above (in this case SUM(B5:B8)). Now, I want to know how what I spent compares to the amount budgeted. I’m still working on getting estimates from vendors, so I want the budget status to change automatically if any of the prices change.

If the predicted expenses are $1,000 or less, I want the budget status to be “On budget”. If the predicted expenses are greater than $1,000, I want the budget status to be “Over budget”

1. Click on the cell where you would like to add the formula, in this case B11.

2. Type the equals sign then IF and an open parenthesis to start the function like this: =IF(

Note: the function can be capitalized or all lowercase, it doesn’t make a difference. You may see a green popup appear with some more information on how to format your function (see below). If you don’t see this and want to, click the small blue question mark icon immediately to the left of where you are typing.

3. The If function if formatted like this:

=IF(logical expression, value if true, value if false)

Here’s what each component of the function means:

logical expression is the condition that you want to check in the function. In this case, we want to know if the predicted budget total is greater than the approved budget of $1,000.

value if true This is what you want the function to show if your logical expression is true. In this case, if the predicted budget total is greater than the approved budget of $1,000, I want the function to return “Over Budget”.

value if false This is what you want the function to show if your logical expression is false. In this case, if the predicted budget is not greater than the approved budget of $1,000, I want the function to return “On budget”.

Note: the value if true and value if false, can be a lot of different things. You could set them to equal another cell by entering the cell ID, for example A1, B9, or J288). You could also have them return a fixed number by typing the number directly, for example 2, 16, 482, etc). Lastly, you can have them return a word or phrase, this is what we’re going to do here. If you’re using a word or phrase, the text needs to be in quotation marks.

4. Enter the formula that you need for your project. Be sure to end with a closed parenthesis. In this case, here is what the final formula will look like:

=IF(B9>1000,"Over Budget","Under Budget")

Notice a tiny popup may appear as you’re typing to give you a preview of the result. See where it says “under budget” in the popup below.

Note: I could have used B9>1000 or B9>B1. It wouldn’t have made any difference since B1 is where I entered 1,000 as the total approved budget amount.

5. Hit the enter key on your keyboard to see the result of the function. In this example it should be “Under budget”

6. Now, let’s say the price of decorations has increased to $175. I updated that amount in cell B5. As soon as I hit enter after typing 175 into cell B5, the budget status automatically changed to “Over budget” (circled in green below).

This is happening because the If function that you entered checked to see if the total projected expenses (B9) is greater than 1,000. So when I changed the cost of decorations, the total projected expenses changed to $1,025 which is greater than $1,000. This made the If function true, so it returned the “value if true” in this case “Over budget”.

7. You’re done! You’ve now successfully used the IF function. There are so many potential use cases for this function. Keep it in your back pocket to try in different situations and formulas.

Note: The If function works really well with conditional formatting. If the example I gave above were a real event budget, then I would have applied conditional formatting to the cell with the budget status. I would have made it so that, if the status was “on budget” the cell would be green and if it was “over budget” it would have been bright red. 

This is helpful when you’re using a spreadsheet for top lines when things will update frequently. So, when I open the spreadsheet, I’ll notice if the cell is red and I can address the budget issue that is causing us to go over budget.

8. For more information, see the official instructions from Google Sheets on the IF function here.

Countif function

The Countif function is similar to the Counta function explained above, one key difference is that this function counts conditionally. It will only count the cells that meet the criteria you give. In this example, I want to know how many volunteers requested hats and how many requested t-shirts.

1. Click the cell where you want to enter the function. In this case, E3.

2. Type an equal sign, the function name Countif, and an open parenthesis. Like this: =Countif(

3. The Countif formula is formatted as follows:

=Countif(range, criterion)

range is the group of cells that you want to count if they meet the criteria.

criterion is the logical condition that you want to use to count the range.

4. Enter the formula that you need for your project. Be sure to end with a closed parenthesis. In this case, here is what the final formula will look like:

=COUNTIF(B3:B20,"Hat")

5. Hit the enter key on your keyboard to see the result of the function. In this case, 6 people selected “hat” as we can see in cell E3.

6. Now, let’s repeat the function to see how many people selected “t-shirt”. This will follow the same steps, though the function will be entered into cell E4 and instead of “hat” the function will say “t-shirt” as the criterion. As you can see from the below example (formula circled in green), 10 people selected t-shirt (result circled in blue).

7. You’re done! You now know how to count cells based on a set condition. Similar to the above functions, this is a good baseline one to know. You can use it in a lot of different situations alone, or combined (aka “nested”) with other functions.

Note: If you want to do more complicated Countif functions that are based on multiple conditions (for example if someone selected a hat and had a box checked for attending the training), then use the Countifs function. Countifs functions just like Countif, but it allows for more criteria. 

8. For more information, see the official instructions from Google Sheets on the Countif function here and the Countifs function here.