Google Sheets IF & IFS Functions - Formulas with If, Then, Else, Else If Statements
ฝัง
- เผยแพร่เมื่อ 29 ก.ย. 2024
- Video tutorial about Logical Test/Expression, IF function & IFS function in Google Sheets.
In this tutorial we'll cover how to use formulas with If, Then, Else, Else If statements using IF & IFS functions in Google Sheets.
Google Sheets
www.google.com...
Website:
www.chicagocom...
This video just sparked a thousand ideas!!! Thanks a million!
I used to try to find -and watch- the video that applied to my sheet, before I started working on it.... Now I just watch ANY one of this guy's vids. Its a great help to my process,to just watch him methodically "logic"things out. It's been a huge help 👍😁
How you double click and send it down?
Thank you so much! Super helpful!. Amazing explanation, thanks a lot..
How to mark a student if they are weather excellent, good, ok or bad using their grades.
Example student "A" got 10 points wich means he is exellent. If he got 8 means he is good, and so on.
The table columns are telling the result of the students: Names , Grads, Excellent, Good, Ok, Bad.
The table rows are showing 100 of students' names. and grads.
The question is how or what function to use to let google sheet determine the students' behavior..
Hope you understand what I means. Thank you.
AMAZING!!! This is the exact information I have been searching for for days and it's well explained!!
Thank you for doing these videos. Do you have a bitcoin wallet that we can send donations to?
Did you save some for yourself
Thanks for this. Google's own help documentation with the square brackets surrounding additional if targets was what was causing mistakes, your vid showed I didn't need them and it worked.
I fucking hate excel its not working
Thank you, thank you, thank you. I have scoured the internet for days looking for an explanation of "if then else" statements and / or the understanding of "ifs" statements. The help documents are worthless. I have subscribed and feel I will be watching your videos often. Short, clear, and on point. Please keep up the good work.
Oh my God, why did it never occur to me before to search TH-cam for this??? I am OBSESSED with Google Sheets lately and using it for everything. And my hunger to know more is growing as my data needs expand. What a treasure trove! Thank you! By the way, I adore your accent. Are you Dutch? I dated a guy from the Netherlands and it sounds like the same accent. 🙂
Thanks for introducing IFS for me. Great tip.
Nice Vid! Excellent instruction. One question: Can you make that sample Logical Test worksheet available? It makes it easier for the starting Sheets student to have a sample table available. Good job!
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?
Works with non numeric values too! ie. ifs(D2=Adidas, 1, D2=Ralph Lauren, 2, ... TRUE, 0)
Thanks for the help!
Excellent explonation, many examples, easy to understand. Thank you so much for this, we really apprieciate it.
Can someone help. If my if statements were to show numbers. For example =ifA1=5, “55%”. So the cell says 55%, is there a way for me to graph that 55% and display it?
Would you be able to help? I want sheets to recognize Jon in column A, take column J dollar amount, and multiply . 75 into Column O. Would you be able to put a formula for that? Thanks
for =if 'sales' and 'cogs' are > 10, for example, return 'yes' or 'no' for true or false respectively. how do i command it to do that?
*Your videos are really good.* All the best. Please, could you help me to solve the situation so that after entering the given function in its entirety, we turn it off so that there would be no later change. Let's say that occurs with the price of a product, when I change it, it creates chaos in the previous state. Thanks in advance for the reply.
If possible to make push notifications if a google sheet that it dynamic feeds it self whit external data using conditionals operators. I mean can you run scripts for example . Example I got number in red then I send scrip to interact whit other app for example? Or example I got a sensor value true or false if is true then exec (script) made to set off alarm for example or start recording then can access from my Google sheet link to see the record
Does anyone know how to help me using this along with dates. What i want to do is basically have a spreadsheet for tasks and have for example that from todays date i have 30+ days i can output 'less urgent' and if 1 have 14 days to 'keep an eye' and then 7 days or less 'urgent' . What i want to do is have the task deadline dates in the spreadsheet and then all i would need to do is look at a quick glance at the output above and know what needs to be done urgently and what needs more time. If anyone could help me with this i would be really grateful. Thanks
Excellent. Doing a great job indeed. I want to contact you personally over phone. Can I have it ? Please.
Loved the video supper helpful! I have a question is it possible for a formula that says "if column B from sheet 1 matches Column A on sheet 5, then column B from sheet 5 would copy and auto-populate in Column A of sheet 1"? or something similar haha, do you ever take requests for videos? I would love that one haha
Thank you so much! This makes things so easy, i'm able to do things I never thought of doing.
I'm not sure if anyone will ever read this comment, but why did he put 1 before the formula in margin profit?
Please some buddy help me i want to make one formula i have 1 digit if it is less than 1.5 it is considered as 1 if it is greater than 1.5 considered as 2 what formulas should I use?
im trying to do if=KS=Kansas then outcome for the box = Kansas. Is there a specific way to do this? it may not be an if function
What should I do if I only need to get the total sales of Adidas brand? What formula should I enter?
Thank you
WOW!! Best video ever! Thank you sooooooooooooooooooooooooooooooooo much.
thanks
nice
You're awesome brother. love your videos!
Very well explained! Thank you!
I'm a trainer and I commend you for your EXCELLENT training! You've explained this CLEARLY and illustrated it well. Thank you! I've subscribed. ;-)
man, your vid has the most ads appear that I ever watch. XD
Why did he subtract 1 from the COGS?
very helpful thank you!
I need help, I can't find it in any tutorial, how do I auto-populate a column in another tab using a drop-down list ( data validation) in the first tab? I just don't want to copy and paste a column to another tab anymore ( all in the same sheet). For example, the first tab is the master list of bills( amounts, date paid, credit card number, etc) , and I have more than 10 other tabs that are per property. So if I enter information in one column in the first tab, it will auto-populate the tab for that property.
Maybe this? th-cam.com/video/QTySwuhpHG0/w-d-xo.html
how if i want something like B1="norman simmons",C1=IL then E1 will be 300$ ? is it possible ?
AND th-cam.com/video/ihpeWzWBjDU/w-d-xo.html
I am studying for Google Educator certification. This was very helpful!
Could you help me embed a editable Google sheet in blogger..
Is it possible to a) tell our IF function to don't do anything if logical test is false, and b) use IF functions as conditions for cell formatting? I would like something to the tune of if X < 0, make font red.
Yes. th-cam.com/video/NhEqCqt-nl4/w-d-xo.html
Thank you so much this video helps me alot
Hi, can u help me with my project please?
I am trying to have an ifs function check 2 values. I tried to use the and function and it worked for the first logical test but it kept making me end with a ,0 at the end if the first logical test. Not sure if i messed up the phrases
=ifs(and(e17="yes",d17>=1000),d17*0.2,0,and(e17="yes",d17>=500,d17*0.1,0). If you could hell point out my error that would be much appreciated!!
You are so perfectly clear in how these functions work, and in describing as many possible scenarios where it coul dbe used and where we could encounter bumps. So appreciated - awesome video. BUT!!! QUESTION!!! I can not seem to add up this new "ifs" column. After successfully entering an ifs formula that gives the cells a numerical value, I now want to add up all these values. I've tried the basic "sum(a1:a25)", but it keeps giving a value of zero. I also am struggling to find a google search relevant to this exact issue, as I keep getting results for "sumifs" and "countifs", and that's not what I'm doing. Any ideas?
it is likely because the values in the "ifs" column are not actually numbers.
The IF function can return either a value or a text string, depending on what you specify as the value_if_true or value_if_false arguments. If you have any text strings in your "ifs" column, the SUM function will not be able to add them up.
To check if this is the case, you can try using the VALUE function to convert any text strings to numbers. For example, if your "ifs" values are in cells A1 to A25, you can try using the following formula:
=SUM(VALUE(A1:A25))
This will attempt to convert any text strings in the "ifs" column to numbers, which can then be added up using the SUM function.
If this still doesn't work, it's possible that there may be an error in your IF formula or that there may be hidden characters or spaces in the "ifs" column that are causing issues. In this case, you may want to try copying and pasting the values into a new column or using the TRIM function to remove any extra spaces.
That was SO helpful and exactly what the information I was needing. Thank you!
I used the formula: =IF(E10=TODAY(), "1") I wanted a field to have a number value ("1" in this formula) when the date of another field matches todays date, the E10 cell in this formula.
So, basically the formula says: When today's date = E10, place the text "1" into the field to which this formula is applied.
For my sheet, the "1" is a NUMBER field that gets added to another filed, that sums to add a dollar value to another field via another formula.
Such pathetic when I put timings as range . The formula works well with excel but not with google.
What formula?
18:03' other as result of logical test
Ahhh that saved my life haha. was going mad with this problem
how dot make colour when the number is greater and when is smaller? that would be great
Conditional Formatting th-cam.com/video/mgW0t5mu_As/w-d-xo.html
Not (,) in formula. Use (;) instead. Or is it only in my Google Sheets?
Depends. This video should clarify it th-cam.com/video/ljvB2arCsNQ/w-d-xo.html
Thank you very much .
Sir , How could i move the entire row automatically from one tab to another if value is true and remove Row from existing tab?
If anyone see my comment and know how to do this , Please let me know.
Cause i know how to import range or data from one tab to another tab in Gsheet,
But how to do this - if the value is true then it will move entire row automatically from this tab to another tab.
Thank you very much if anyone help me to find this
Can you use this function to make change from a sale? Say someone bought a $0.13 item and paid $1.00, change would be $0.83 but the function would return the coins, ideally 3x0.25, 1x0.05, 3x0.01 or 3x.025, 0x0.10, 1x0.05, 3x0.01
To take it a step further could you have a table of existing change in a drawer and divvy up the coins based on the quantity? Something like you had no 0.05 coins but had 47x0.01 coins so you would end up giving 3x0.25, 8x0.01.
Hello, thank you for this video. I can't seem to find instructions for my specific needs. I am trying to assign a point or value system to text. So if the text is "APPR" each of that text name would = 1 point, and then I need to get totals for each text value. Please advise.
Hello, I need to do a commission bracket calculation and I would like to hear your advise
the bracket goes like this:
$0 to $24999 is 4% of the sale
$25000 to $29999 is 5% of the sale
$30000 to $34999 is 5.75% of the sale
$40000 to $44999 is 7.00% of the sale
$45000 to 49999 is 7.50% of the sale
$50000 and over is 8% of the sale
Thank you in advance!!!
Hi, I need a help. For example, I have added Value=X Column A row 1. Also I have added Value= X Y Z on column B row 1.I want to get remove X from column B. Can you help me to sort this out?
I appreciate your teachings! Thanks for sharing your amazing talent in manipulating data in google sheets. Though i have a quick question if it’s okay. If what’s the best formula to use if your looking to count the accuracy averages. Like if you want to know if what’s the average of those who answered same with mine and those who have different. Like i if i put yes, id like to check the average of those you answered yes and those who answered no.😊
your tutorials are great! how would you get a google form to give a message "youve already applied "when submitted. the criteria to be checked first name, surname, middle name, and 3 different ID 's like national id ,drivers permit no. and passport no.?
Thank you so much! Super helpful!
Glad it helped!
Okay so say i'm looking at dimensions and want to use the dimensions to get a value from another sheet. for example. If side a is less than or equal to 10 inches and side b is less than or equal to 10 inches, and side c is less than or equal to 5 inches then cell B3, If side is from 10 to 15in, side b is 10-15, and side c is 5-10 then cell c3 etc... how would i go about building that?
Can something like the IF function be done when there is a data table that I'm comparing it to. Like, if you had a tab where it only said The Brand Name, and the associated Product Category. Could the IF function (or something else) be used on another tab so that when you enter Adidas, Low-End Product would appear to a cell nearby? I'm trying to make a table pull fixed data on another tab.
Hello. I was wondering how to create a formula that adds all the negative numbers in that column together?
SUMIFS th-cam.com/video/AKs3aEtaEKw/w-d-xo.html
So I have 1 Cell on One Sheet I want to Show 2 cells from another sheet if they're not blank.However if one is not blank and the other is blank I want it to show the None Blank Cell and if the both blank i want it to Show up as Blank. How would I do this and What would the Formula Be to do it? Im having some trouble on this so if you can help it would be much appreciated!
how do we apply an if function to multiple cells on the spreadsheet instead of just one? and not by dragging down. i'm trying to apply it to multiple different cells that may not be on the same column.
You need to provide an example. I don't understand what you're trying to do.
Learn Google Spreadsheets sorry about that. let me better explain it. I'm trying to find a way to apply this function =IF(B4>0,B4,2) to multiple cells in multiple columns at the same time. currently the only way i know is to paste it into 1 cell at a time but that's tedious because i have a lot of cells i need to apply it to. i was wondering if you knew shortcut trick to paste it into many unique cells at the same time. I'm saying unique cells because i already know there's a fast way highlight a row of cells in a column and paste, but I'm not trying to paste in a straight row. the cells I'm trying to paste in are uniquely scattered out and across various columns. so i need a way to selectively select those cells and then paste. thank you
Game Casters use the or function my friend, here I’ll give u an example:
If b1 is 3 and I want b2 to tell me if b1 is equal to 5 or 6 or 9, I’ll type this.:
*=or(b1=5,6,9)*
That function will tell me if b1 is equal to either 5,6 or 9.
Lo Low thanks for sharing this!, that's also very helpful :)
Game Casters no problem👍🏾
Oh my God, it was so simple, goddammit! Thanks, you saved my brain.
can I aply this to a text in a dropdown
Could you do if statements for Google scripts? It's a little bit different when you throw Javascript in the mix and I'm have a tough time.
Usage Score (based on Active Student %)
10 More than 80%
8 60-80%
6 50%
4 30-50%
2 1-30%
0 0%0
I have to apply this usage score in the formula...how can I do it? Could u pls help me?
Use VLOOKUP
Thanks for making it clear
SIR I WANT TO KNOW THAT IF SALE VAL IS LESS THAN THREE LAKHS PAY DUE DATE IS 10 DAY FORM DA TE OF SALE AND IF SALE VALUE IS MORE THAN THREE LAKHS PAYMAMENT DUE DATE WILL BE 15 DAY SIR PLEASE REPLY HOW TO WRITE FORMULA thanks
Great tutorial, and super helpful. I was wondering if you could offer some advice with a challenge I'm trying to tackle:
I'm building a "Thank you" email that will be sent to a new customer who placed an order, and for this email to also serve as an onboarding kind of "start up guide" to help the customer get familiarized with the process ahead.
The challenege is we have multiple products, each product has multiple package options, each has different lead times and conditions based on whether it's a US customer, or from outside the US. Also, they could either have fully paid or have remaining balanace to be cleared before the product will be supplied.. this goes on and on...
The end result I'm shooting for is a range with the relevant text, with categories and text body sections in specific font styles, size and spacing , that I can print to PDF and attach to an email.
As you can imagine, going at it with IF and IFs I keep hitting a wall, both with regards to managing all the possible conditions without ending up with a formula from "hell", as well as maintaining the font style size and spacing required.
Is that impossible to do with google sheets?
Ok, for me I started to notice errors when I add large numbers together. Why is this and how would I fix this?
if the box contain upper case and lover case how can i accept both either lover or uppercase as answer. if student write a lower case answer i can accept if eithe also i can accept
It''s not case sensitive by default.
why always error for me (IFS)
Your tutorials are really Helpful..!
But i need this Datasheet ,
will you please provide me the link to practice with that
Can I use multiple conditions to auto-populate a cell on another page? For example, If I have a list of transactions that have dropdown menus to categorize the expenses on the appropriate tab, can I give a date range and category selection be a qualifiers to populate the appropriate sheet?
Exactly what I was looking ofr, thank you!!!!!!
:)
So if I have to create formula for seniority levels like 0-.1= junior etc so how I should formulate this?
Is there a way to use the =today function ? so if a1= "hi" then it will display todays date in the cell I am typing my formula in ? I get parse error
can you show how to use IFS function for a free text.
for example i have a detail column and that's apply for division, so i want to bring all the answer in one column, is that possible?
I'm lost, I don't understand what you're trying to do.
It's probably translation issue.
@@ExcelGoogleSheets hemm, can we use ifs function for a text but the text is not constant?
Me encanta la explicación y los ejercicios! Agradecida por estos vídeos 😃
isn't it Profit margin = (Sale - COGS)/Sale ?
yea, looks right.
Awesome tutorial! But, I was wondering if we could delete a row if it was blank or had no data in it.
Can you help ? I have created an expense income sheet. When an expense is made by credit care, I would like to the sum to subtract from the row which contains the running credit card balance as well as subtract from the row which contains the overall balance.
If at the end of a formula the condition is not met how do i get to enter a certain value without losing the formula? Is it possible?
IS there a way to create an IF statement to move information from one column to another, so if there's something in column A then move it to Column AB
Hi! I was looking for advance print tutorial in your channel..this was the first video that pop out.. Do you know how to do advance print?
I don't think I have any "printing" related videos. Sorry.
Just go File-Print and follow the dialog.
At 11:20 if you just use control shift down and then control d it will overwrite the column with new cell formula
11-03-20 13:00 and 15-03-2020 15:00 how do I ask it if the result is less than 14 days 0 hours 0 minutes 0 seconds? to display YES This is to work out a refuinds formula. So if say someone ordered on 01/01/20 00:00 then if the cancellation is 14 days and they tried to cancel at 15/01/20 00:00:01 returns value NO. If you can solve that as it is the only thing I am struggling with. Done all the =QUERY(IMPORTRANGE etc. Many thanks
=IF((B1-A1)
@@ExcelGoogleSheets hi thanks for that but got this error: function minus parameter expected number but cancel date is text and can not be coerced to a number. I tried changing the format to number the date turned to 45,319. Thanks for the speedy reply. I think the time is throwing it out
@@ExcelGoogleSheets is ok I was doing row 1 which are headers. Soon as swapped for 2 all good. top banana
Thanks a lot for this awesome video. This really helped a lot.
:)
all I gotta say is thank you so much for making my life so much easier!!!
Is there a way I can a number bold or change the in a cell if the value is less/more than or equal to another cell?
Hi, thank you for sharing your knowledge. Question: I practice on my computer the if function but for some reason, it can not multiply or divides inside the if function. Do you ha an idea what it could be? or anyone can tell me what it could be. Thank you in advance.
Excellent! Great information to learn more. Thank You. :)
Have you a tutorial covering roundup numbers in Google sheets
Thanks 😊
Tutorial was very explicit.
Hello is it possible to add a cap margin value indicator in a formula??