Lesson 10 – Quick and Easy If Statements in Google Sheets/Spreadsheets

Introduction:

If you are familiar with any programming, it is likely that you have heard of “if statements.” If you have not, an if statement is exactly what it sounds like. Through an if statement, you tell a computer that “If X happens, then do Y.” Not surprisingly, Google Sheets has its own version of an if statement, through the =IF() equation. Through this equation you can tell a cell to be a certain value or word, if a certain criteria is met. That being said, if you want to view any of our older articles, feel free to do so here.

Formula Lookover:

The =IF() equation in Google Sheets is as follows:

As always, we will go through what each of these terms mean:

Logical Expression – This is the criteria of the if statement. What you enter here dictates what will be seen as satisfying the statement (if it is true), and what doesn’t (if it isn’t true). What often entered as a logical expression, is if a certain cell is greater than a number, equal to, etc. However, as long as it can be read as a logical criteria, then you can use it in your =IF formula. The possibilities are truly endless.

Value If True – This is what the cell should display, if the logical expression is deemed to be satisfied. You can put anything here, from a number, to text, or even the value of a separate cell. You can even do mathematical equations or other formulas, the possibilities are endless. One good thing to remember is that if you want it to display text, you have to surround the text with quotation marks, otherwise it will not work. 

Value If False – You likely do not need any explanation, but it is the same thing as “Value If True”, you only enter what the cell should display if the criteria is not satisfied.

Let’s take a look at a hypothetical example of an =IF() formula, and remember to read it as a sentence. So:

If we try to read this as a sentence, it goes as follows. “If Cell A3 is greater than 4, display the text “Value is greater than 4”, if it is not greater than 4 then display the text “Value is less than or equal to 4.”

Good Tip:

If you want to display a blank cell, or make the logical expression look if a cell is blank or not, put two quotation marks next to another, like so “”. In Google Sheets, if you enter two quotation marks without any space in between, it means that there will be no text or value in that cell.

Application Example:

Let us go through two examples, to show you how if statements work. Here is the first example:

We have a list of numbers, where we want to evaluate if they are greater or less than 4. Therefore, we use the equation in the right hand side of the photo. If we read it as a sentence, it says that “If the cell is greater than 4, display the text “Value is less than 4”, if the criteria is not true, display the text “Value is greater than 4”.” 

The final product is that with the help of some fancy dragging and conditional formatting, we were able to easily evaluate if the number is greater than 4 or not.

Here is another example, just this time we are evaluating text, not numbers. As you can see, the formula is quite similar to when evaluating numbers, just theres a small trick. As text is not a number, you have to surround the text in the criteria section with quotation marks. If you write =IF(Cell = Hi, …), the formula won’t work. Therefore, you have to surround the word Hi with quotation marks, like shown in the photo. So, a good rule of thumb to remember:

If it’s a number, let it be. If it’s text, surround it with quotation marks.

With this, you know how to do basic if statements. If statements in themselves are simple, but you will be able to build on their usefulness more and more, as you become more familiar with Google Sheets. Now, let’s get into some more interesting variations of IF statements built into Google Sheets.

More Advanced Features

Google Sheets, as you may know, is all about being efficient and effective. This is why it has built in combinations of IF Statements, and other useful functions. For example, lets say you wanted to take the sum of a set of numbers, only if they are greater than 4. 

One way you can do this is by writing this awful if statement: =IF(Cell > 3, Above Cell + Current Cell, Above Cell). If you have no idea what is going on in this IF Statement, that’s totally normal. This is why Google Sheets has a built in =SUMIF() Statement. Here is a good list of formulas that use built in IF Statements:

• =SUMIF()
  • =SUMIF(criteria range, criterion, [sum range if different than criteria range])
• =AVERAGEIF()
  • =AVERAGEIF(criteria range, criterion, [average range if different than criteria range])
• =COUNTIF()
  • =COUNTIF(criteria range, criterion)

As you can see, the structure of these three formulas are all highly similar. This is what the terms all mean:

Criteria Range: The range of cells for which the criterion is tested against. This usually for most simple spreadsheets, is also the range for which you will count, average, or sum.

Criterion: This is more or less the logical expression for which you test the range against. The BIG thing to remember is that you are not testing a singular cell, but rather an entire set of cells. So rather than saying CELL > 4, you have to say “>4”, and surround it with quotation marks. This is because Sheets will go through each cell, and enter it into the statement you specified. We will get into this later in examples. 

Average Range: This is an optional part of the formula, and should only be used if the average range is different from the criteria range. This is a more advanced aspect of these formulas, so we will not go into them now.

Examples:

=COUNTIF():

This is arguably the simplest of the three. You give criteria, and it counts how many cells match that criteria. Let’s look at this example below:

Here we have a group of numbers, and we want to count how many are above the value of 3. For this, we use a COUNTIF Statement. The formula is as shown in the photo, =COUNTIF(D3:D10, “>3”). This looks within the range D3 – D10, and counts each cell which is greater than 3.

If you notice, the criteria statement is surrounded in quotation marks. This is because you must specify the equation Google Sheets will check each cell, and substitute into. 

So Google Sheets will go through the range D3-D10, and substitute into “>3”. So it tests if D3 > 3, D4 > 3, etc. If it is greater than 3, it counts it as one. That is how it counts that 5 cells are greater than 3, and if we were to check that, it is true.

From here, the structure is the absolute same for AVERAGEIF and SUMIF. You specify an area of cells to test, you give the test criteria, and it will either average or sum the values that fit the criteria you want. 

Conclusion:

We hope that you enjoyed today’s lesson. If statements and their variations are a very powerful skill, that will help you a lot in your future projects. As always, feel free to watch the commentated video below. If you wish, you can view Google’s own article here. Lastly, we are coming to the end of our lessons, but regardless, happy spreadsheeting!! 🙂