Google Sheets IF Statement: Examples with If, Then, and Or
HTML-код
- Опубликовано: 4 ноя 2024
- The IF statement in Google Sheets tests against a condition or conditions that you define and returns a result, based on if the condition is true or false. Data aggregation, dashboard visualizations, and other spreadsheet tasks will soon be a whole lot more efficient in your world.
► Learn more in this complete guide to the Google Sheets IF Function: coefficient.io...
Video Timeline
00:00: IF/THEN Statements Google Sheets
00:24: Calculating Commission Rates
01:05: Logical Expression in Google Sheets
02:02: Google Sheets IF Statement Multiple Conditions
02:37: Google Sheets Nested IF Statement
05:49: Expand Formula View in Google Sheets
06:50: IF/OR in Google Sheets
► Come see us: www.coefficien...
Any questions? Comment below; we have Google Sheets experts on the other side.
#googlesheets #ifthen #ifstatement #spreadsheets #coefficient
Good day, I have been searching for someone to explain formulas as good as you for so long! Great examples but I couldn't find the solution to my existing scenario. I want to use the formula "IF" this way: if text in the cell C5 equals/matches "text" then the following calculation should take place G5+G6. I am not sure if it has something to do with "IF" formula in specific, but I WOULD APPRECIATE big time if you advise which method is better.
Thank you for your kind words - We're glad you find these videos so helpful!
Regarding your question, here's how you can use the IF() function to accomplish what you're looking to do:
=IF(C5="text", G5+G6, "")
This formula checks if the text in cell C5 is equal to “text”. If it is, then it adds the values in cells G5 and G6. If not, it returns an empty string (you could replace the empty quotation marks at the end with any other value, text or formula).
Thanks a lot for your input and help! You are amazing!@@coefficientio
thanks so much
Hello, I am doing a QR code inventory set up, where people scan the QR code and fill out a google form saying which item they took and the amount. I have it setup where the responses from the google form goes onto google sheets. What I want to do is take that responses quantity and subtract it from the total amount ( which is on another sheet) in the inventory. How can I do this ? Also, I have over 20 items on the google form.
Hi - it looks like you might be trying to calculate week over week submissions. Here's an example of how to do this: drive.google.com/file/d/1yOoOCVXnwP3NNuhdjZzdOsyIC_Lw3j8T/view?usp=sharing
If you need to also calculate by 20 items (assuming they're inventory items) you can also add this information into your pivot.
Here is the million dollar question. This is function from one of my cells: =if(ISNUMBER(B10), INDEX(B:B,10,1)) . It will return number and that works. But if text shows up in the same cell, it will show FALSE. Or if I put in formula " ", it will show nothing. Is it possible somehow that it doesn't return FALSE. If the statement is FALSE, just don't change anything, just exit, without changing contents of cell. I have one cell that sometimes shows text and sometimes numbers. I'd like it that it keeps the number, but if text shows up, it doesn't change anything (IT LEAVES THE PREVIOUS NUMBERS, IT DOESN'T CHANGE THE CONTENT OF CELL IN ANY WAY SHAPE OR FORM).
Thanks for your question!
Unfortunately, IF() statements will always update when the cell they are referring to change.
You could try one of these solutions:
1. Add more formula logic so that if your IF statement is FALSE (ie if B10 is text), then tell your formula to find the previous number (we don't know your exact context, but maybe that number would then be one cell above, in B9, for example).
2. A more advanced solution would be to write an Apps Script that you can use instead of a formula, but this requires advanced spreadsheet and java knowledge.
I watch all the videos but i just cant figure out how to do this. I have a simple spreed sheet that i want to subtract some numbers i am trying to figure out how to write the if/then so that if the answer is less than 0 then the formula just says 0 and if the answer is greater than 0 it puts the answer.
Let's assume that you're subtracting cells A2 and B2 with the following equation: A2 - B2. If you want to show 0 if the result is less than 0, and show the actual result if it's more than 0, then you can use this formula: =IF(A2-B2
I can't figure this out. If cells F2 and G2 are blank, then leave cell J2 blank too. However, if cell F2 and/or G2 are not blank, then F2+G2. How would I write this? In essence, I would like a blank row unless raw data is entered into cell F2 and/or Cell G2, and the sum of F2+G2 to appear in J2.
Thanks for your question!
Here's how you can use the IF() function to get the result you're looking for. Try it out and let us know if it works for you.
=IF(AND(F2="", G2=""), "", F2+G2)
what if I want the true/false answer to be determined by subtracting two fields? for example Field A and B if B>A then I want it to say "gain" and subtract A from B If A>B I want it to say "loss" and subtract B from A would it look something like =if(B>A,"gain"A-B,"Loss"B-A) needless to say this didn';t work. What am I missing?
Hi @painfullyhonest - you can use the formula in this sheet that I built with Coefficient's free formula builder for you. I've provided a screenshot of my prompt and the output.
docs.google.com/spreadsheets/d/10C_OGaGLulQQNH8ct2BAOtLMIZsT91EuaBL-QY5REnQ/edit?usp=sharing
@@coefficientio thank you so much for putting the effort into that and that is kind of what I have right now what I wanted is for it to say gain and then a number next to it of how much that gain was or the word loss and how much the loss was and I can't figure out how to do an if statement where it allows for a word and a computation like subtracting a from b or b from A all in the same cell
@@painfullyhonest there is likely a more sophisticated way to do this all in one cell that I'm sure our spreadsheet experts on staff could do, but I've added this into the sheet and you can then simple hide columns c and d if you don't want them visible.
In column D, you'll also see that I've added conditional formatting that visually would show a gain or a loss, where a flat value (no gain or loss) would just be a white cell. This could also be another alternative without requiring the word gain or loss in the cell.
thank you I see what you did there and yes I decided to take the words out and just have two colors. So I have the number with a - being negative instead of saying loss. It works. Thanks again for your time
@@coefficientio
@@painfullyhonest happy we could help!
Hello- I want to find % of students (out of 20) who passed my assessment. If 75% or greater pass, the cell turns green signaling me to move on to track the next learning standard.
Will you help me? Thanks.
You should use conditional formatting - here's a video for this!
ruclips.net/video/z3E9pmK1C0M/видео.html
THANK YOU!! I was about to punch a wall, I've been so frustrated with this!
This makes us so happy! You're welcome. Glad we could help you (and the wall) ;)
how to write a if/then formula where a range of #'s equals a letter (specifically 0-59=C, 60-79=B, 80-100=A). For example, If(B12=0-59,C) etc
So sorry we missed this!
Great question! To do this using the IF() function, you can use a formula similar to this one with multiple nested IFs. Add as many more IFs as you need to meet each of your criteria for ranges of numbers:
=IF(B12
I was losing my mind watching all these tutorials, apparently now you need to use a semicolon to separate arguments.
Hopefully we helped guide you in the right direction!