Jon, I just have to say what an awesome tutor you are! It's partially about what you reveal but mostly about how you do it..... You are as logical as an Excel spreadsheet but > it is your ability to express the process with concise clarity that makes you excel. I have learned - and more importantly, understood, so much through your tutorials. Keep on, keeping on - ad infinitum!
Formally studying Excel for an apprenticeship, and these videos are a real help. The videos on the apprenticeship aren't this good, and having missed a live session, I was really struggling
I had to come comment because I couldn't find the answer for the nested IF statements for a project I am working on for a class, but you were able to make it very clear on how to write them. Thank you so much, I was getting so frustrated!
As always Jon......excellent. You are a natural teacher. Just the right pace for newbies and no padding in there at all. Concise and easy to understand. Thank you.
I have a job interview coming up that will include an Excel task, so I need to brush up on my skills and knowledge. This video, along with your others, is handy, clear and concise. Many thanks, Jon.
Watching your videos always feel like having a teacher patiently teaching me one on one, thank you for all the clarity and contextual explanations, dear Professor Jon :)
Oh you are the best formula instructor ever for me. contrary to your kind and step by step illustartions of formulas, other instructors just skipped them and i dont get what they are sayin. thank you . would you like to join in Excel Mos2016 pre exam instructors in Korea, YBM. i do believe they should all fire instructors with no english experiences of excel skills that cannot specify step by step formulas and their languages. i was listening to Korean Excel instructions but with no idea of what they are saying, then i googled here to find out and try to understand of formulas explained step by step here
Hi John nice video, so is the ( IF ) function one you would use if for example you wanted to check the value of say cell C1 $500 and cell C2 $400 , if the value's don't match then perform a calculation etc subtract G2 from G3 ??
Excellent explanation- how about comparing data on one sheet against info on another sheet ( normally I use the vlookup function- can u do it with the IF function too ?) thanks
Hi Jon, tour tutorials are awesome. On nested If, where you have the second IF, after the argument "small", I suspected that you should close and end there. *13:55 to *14:15*. There is no room or option for "false" argument). I did try for the sake of clearing the doughty and it gave "you've entered too many arguments for this function"
Hello Jon Good job man i have a question i formed as an excel file and I'm gonna send it later to if i can match multi different counter condition to give also different results . I know my question seems confusing but in xlsx file i explain it thoroughly
Hi Jon, thank you so much for these videos. They are definitely very helpful. Question for you though….is there a way to look at a group of logical test v. individual logical test to yield a result? I’ve created a enormous embedded IFAnd statement ( to where I’ve reached the maximum allowed by my version of excel) but I need to figure out a way to consolidate so that the formula isn’t so large. Any insight that you can provide would be greatly appreciated. Thanks
Great video however if we have a list of email address on one tab and first name on another tab what formula can we use to bring the email and the first name of that email address together?
At the 4:45 mark, you say to get a number it doesnt need quotations. I tried and it gives me a false or true result. I need a formula that will provide the difference amount between two sets of numbers in certain columns. So if a number is greater than another number then the result should formulate the difference amount. Hope that makes sense.
I'm on a MacBook Pro using Excel and I'm using the if/and function and even though the value is true it is returning the false value. I've started a new worksheet and pasted all the data as values and I'm still having the same issue. Here is my formula" =IF(AND(Table1[@Type]="Other fee",Table1[@Description]="Ad Fee Standard"), "Ad Fee Standard","") Basically, Table1 has a 'Type' column in that column the word "Other fee" is there, also Table1 a 'Description' column in that column the word "Add Fee Standard" is there - so it should be returning the value Ad Fee Standard, but it is leaving the cell blank. I would welcome any insight, as I'm quite frustrated as to why this will not work.
If we have 3 columns with Schedule start date, Schedule end date and Actual end date. Can we find the number of days between two dates based on text from a cell .. if text value is “resolved” I need the date formulas as current date minus scheduled end date. If status is “resolved late “ then Actual end date minus schedule end date.
Hi Jon, I saw you touched briefly on the VLOOKUP, I'm having issues sometimes with the VLOOKUP not being able to pick value sometimes even though there is an exact match with no invisible spaces or wrong characters, I wonder if you experienced that as well and what is causing it... The reason I know the target which I'm comparing is an exact match is because when I copy and paste those columns on a new sheet it all of a sudden pick up the value.... I also make sure the formatting is the same or clearing the formats. A similar issue happens on search function sometimes.
Hi Benny, are the values numbers or dates? If so, it could be a rounding or decimal place issue. Vlookup requires the values to match exactly, including decimals. If the values are text then it will likely be a different issue. We don’t have a public forum but we do have a community forum inside our Elevate Excel Training Program where members can ask questions and post files. You can also submit a question and sample file at www.excelcampus.com/ask/ Due to the high volume we receive, we can’t promise to answer all questions submitted there but do consider them for future YT videos. I hope that helps.
Thank you Jon! Is there an IF formula or is there another function that would allow me to look at how many times "a specific value" appeared and what dates were associated with it? For example: Let's say I have 10 Pickers on my farm and every day the total number of fruit picked are logged, by date, amount, type, and picker. DATE (A) FRUIT (B) Picker ID (C) Amount Picked (D) 10/19/2021 (A) Apples (B) Picker 1 (C) 60 (D) 10/19/2021 (A) Apples (B) Picker 2 (C) 92 (D) 10/19/2021 (A) Oranges (B) Picker 3 (C) 90 (D) 10/19/2021 (A) Oranges (B) Picker 4 (C) 74 (D) 10/20/2021 (A) Oranges (B) Picker 1 (C) 60 (D) 10/20/2021 (A) Oranges (B) Picker 2 (C) 92 (D) 10/20/2021 (A) Apples (B) Picker 4 (C) 90 (D) 10/21/2021 (A) Apples (B) Picker 4 (C) 74 (D) If I I wanted to know how many times the value "apples" occurred for each date adjacent to it within Column A (Date), how would I count how many times Column B (Fruit) returned the value for apples on that given date? I would like a return value that looks at all the dates within the date column where both Apples and a Date occur and count how many times it appears for that date. From my data snapshot above my ideal result would say: 10/19/21 2 (which means there is a total of 2 occurrences for apples on 10/19/21), and 10/20/20 1 (which means there is a total of 1 occurrence for apples on 10/20/21), and 10/21/20 1 (which means there is a total of 1 occurrence1 for apples on 10/21/21). Any help you can offer is GREATLY appreciated!
What if I don't have a table? this won't work for non-table. What if I want to select a range from eg: H6 to H28 and want to show which ones are larger/smaller. That's why when I tutor people, I always think of an alternative because people might not have the same information as me.
Can someone direct me to a link that talks about two cells comparison? e.g. if A1=1 and if B1=2 then C1=2 and if A1=2 and if B1=1 then C1=1 and so on. I have four conditions for C1. How do I write all of them in one formula?
Greetings. I hope you can help me figure this out. I have numerous customers, all with various charges for our servicer. Is there a formula whereby I can set up a column that will automatically enter the amount charged to each customer when their name is entered into column A, then column B enters the amount we charge them? For example, Smith's Flower Shop pays $50.00 per service and Joe's Auto Shop pays $35.00 for the service we render to him. How can I set up a column that automatically enters the amount each customer pays? I hope that that makes sense 🙂. Thanks in advance for your help.
Hi there! 😊 One way is to use a VLOOKUP. Create a table in the same file but on another worksheet with servicers in the first column and known dollar amounts in the second column. For example, the newly created table is on Sheet1 and takes up Cells A1:B10. Then, in Column B of your original table, use a VLOOKUP function. If your first name is in Cell A2, then in Cell B2 enter something like: =VLOOKUP(A2,Sheet1!A1:B10,2,FALSE) That will take the servicer name from Column A ("A2")in the original table, look it up in the newly created table ("Sheet1!A1:B10"), find the corresponding dollar amount ("2" for the second column), and output the exact (FALSE) match to your original table into Column B. Hope that helps! 🙂
Instead of using two carats with x>Cell>y, try using one for each condition, such as x>Cell. For example, try something like IFS(S6<1800, "A", S6<2251, "B", S6<2801, "C", ...). Any number less than 1800 will be handled by the first condition ("A"), so any other numbers would have to be greater than or equal to 1800 and would be handled by the successive conditions.
HELLO JON, I want two fill land type with column a , b , c , d. If A is greater then all 3 then type A or if B is greater then A, C and D then type B or if C is greater then A, B and D then Type C or if D is greater then A, B, C then type D in E2 cell. pls help
Hi Neelesh, It sounds like you can use the MAX function for this. MAX will return the largest value in the range of numbers specified in the function. I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Hi JON, Thanks for your response. I don't want to write largest value in the range. I have 3 columns with different number with title A, B, C AND D. I want to write title name instead of values.
If I understand correctly, You could combine the MAX function with a lookup function to lookup the max value and return the adjacent column. Something like the following. =XLOOKUP(MAX(B1:B10),B1:B10,A1:A10) I hope that helps. Not sure I fully understand the question.
I'm trying to use multiple IFs in a single cell. Not IFs nested inside of each other, but rather one IF followed by another and then another and so on. But everything I tried comes up with an error. What should I do?
Hi Burst! 😊 If you want to join the separate IF results together as a string of text, use the ampersand (&) symbol. You may want to separate the results with a space, slash, or pipe ( | ), which can be done like &" | "& in between the IF formulas. Hope that helps! 😊
@@ExcelCampus New question. The IFs I'm using have results that output blank text. I want to make it so that if every IF in the cell outputs blank text, the cell will show "None" instead of appearing blank. Is there a way to do that without nesting those IFs inside another IF?
Hi Kahshen! If I understand correctly, you probably want to use the SUMIF or SUMIFS function for that. It will calculate the sum of a range based on the criteria in one or more ranges. We'll add this to our list for future videos. Thanks! 🙂
Thanks Jon :), i have an issue calculating total sales delivered by delivery truck, i used the sumifs formula but it gave the wrong answer! can I email you the spreadsheet?
Thank you so much!! I am in a Master's program and I got stuck in one of my assignments and this helped me immensely.
Jon, I just have to say what an awesome tutor you are! It's partially about what you reveal but mostly about how you do it..... You are as logical as an Excel spreadsheet but > it is your ability to express the process with concise clarity that makes you excel. I have learned - and more importantly, understood, so much through your tutorials. Keep on, keeping on - ad infinitum!
Formally studying Excel for an apprenticeship, and these videos are a real help. The videos on the apprenticeship aren't this good, and having missed a live session, I was really struggling
Great to hear your feedback about our videos. 😀
I had to come comment because I couldn't find the answer for the nested IF statements for a project I am working on for a class, but you were able to make it very clear on how to write them. Thank you so much, I was getting so frustrated!
As always Jon......excellent. You are a natural teacher. Just the right pace for newbies and no padding in there at all. Concise and easy to understand. Thank you.
I have a job interview coming up that will include an Excel task, so I need to brush up on my skills and knowledge. This video, along with your others, is handy, clear and concise. Many thanks, Jon.
Goodluck to your interview! 😀
I always find your tutorials simple and easy to follow, Naturally a teacher keep it up
Happy to hear that, Beatrice! 😀
Thank you Jon for showing how to use this really useful function on building simple report.
real excel expert this man, tks goodness for him
Thank you Jon, you are truly a gift. I improved my excel skill by watching your tutorials. God bless you!
Thank you so much! You tutorials were better than the course I recently paid for.
These are really helpful videos thank you. I’ve paid for a course recently and I find your videos much better and understand and practice with
thanks from somalia Jon, they helped me too much.
HOLY CRAP. THANK YOU! YOU ARE SO EASY TO FOLLOW!
Great Tutorial On What I Would Class As One Of The Most Important Functions To Learn In Excel..Thank You Jon :)
Thanks Darryl! 👍
An excellent description of the IF formula usage. Thumb up!
This is the best tutorial. Thank you
Thanks for the video Jon, will definitely come in handy as a reference video.
Thanks Paul! I appreciate your support. 🙌
Watching your videos always feel like having a teacher patiently teaching me one on one, thank you for all the clarity and contextual explanations, dear Professor Jon :)
WOW! You explain the process brilliantly. Thank you!!
You are so welcome! 😀
Thanks for your video. It was very well explained and easy to understand.🙏
Thanks, Dear Jon. Good Luck, Go Ahead dear . . .
This is the best video I have seen on the topic. Great examples and easy to follow along!
what an amazing tutor, highly organised video, well explained, good demonstration, you have no idea how if feel owe to u
I appreciate that! Thanks for watching!
Thank you very much for this tutorial, I appreciate.
This video is a life saver every time I needed a refresher on nested ifs. Thank you Jon!
Excellent Job!
i learned clearly and sharply use of inbuilt function of IF
Great explanation. Very detailed.
What a refreshing of knowledge !!! Thanks Jon ...keep sharing please
Really enjoyed this one and the pace of explanation.
thankyou sir your content is really amazing and helpful plz keep posting them
Thanks for your support!
Needed this video! Thank you for sharing your knowledge.
You are so welcome! 😀
Oh you are the best formula instructor ever for me. contrary to your kind and step by step illustartions of formulas, other instructors just skipped them and i dont get what they are sayin. thank you . would you like to join in Excel Mos2016 pre exam instructors in Korea, YBM. i do believe they should all fire instructors with no english experiences of excel skills that cannot specify step by step formulas and their languages. i was listening to Korean Excel instructions but with no idea of what they are saying, then i googled here to find out and try to understand of formulas explained step by step here
Thank you, Jon. Your videos are very helpful.
Excellent tutorial! Thank you.
An excellent video, Jon! You explained it all really clearly, thanks.
Thanks Chris! 😀
Thank you so much for such a discriptive video.
Thanks for this training
Jon, love your videos! Thanks!
Thank you for this video!
Really nice, have learned something new today✌✌
You are a Rockstar! Thank You!
Do you have a list of your simplified definitions? I would love that cheat sheet printed at my desk
Thanks for the video as it was very helpful!
this is a good tutorial, thank you sir
thank you, excellent tutor.
You are welcome, @user-jz6yl8cg8q ! 😀
Awesome toturial
Hi John nice video, so is the ( IF ) function one you would use if for example you wanted to check the value of say cell C1 $500 and cell C2 $400 , if the value's don't match then perform a calculation etc
subtract G2 from G3 ??
Great work thanks pleas can you make in come and expenses daily report
Great Work!
Excellent explanation- how about comparing data on one sheet against info on another sheet ( normally I use the vlookup function- can u do it with the IF function too ?) thanks
PS i subscribed ... lots of videos to learn from 😀😀
Thank you Jon.
Hi Jon, tour tutorials are awesome.
On nested If, where you have the second IF, after the argument "small", I suspected that you should close and end there. *13:55 to *14:15*.
There is no room or option for "false" argument). I did try for the sake of clearing the doughty and it gave "you've entered too many arguments for this function"
very good, thanks.
Simple and great
Hello Jon Good job man
i have a question i formed as an excel file and I'm gonna send it later to if i can match multi different counter condition to give also different results . I know my question seems confusing but in xlsx file i explain it thoroughly
Fantastic video ,love you so much , 💖💖💖
Thank you so much!! 😀
Thank you Jon
Nice work
Can you please show us how to make Macros faster in a huge data.
Thank You 👍🏽
Hi Jon. Can I use the IF function to do a calculation if the logical test is yes. Example, if LT is yes A minus B if no B minus A.
Excellent
Hi Jon, thank you so much for these videos. They are definitely very helpful. Question for you though….is there a way to look at a group of logical test v. individual logical test to yield a result? I’ve created a enormous embedded IFAnd statement ( to where I’ve reached the maximum allowed by my version of excel) but I need to figure out a way to consolidate so that the formula isn’t so large. Any insight that you can provide would be greatly appreciated. Thanks
THANK YOU .
Great video however if we have a list of email address on one tab and first name on another tab what formula can we use to bring the email and the first name of that email address together?
Video is awesom.where is excel sheet .
At the 4:45 mark, you say to get a number it doesnt need quotations. I tried and it gives me a false or true result. I need a formula that will provide the difference amount between two sets of numbers in certain columns. So if a number is greater than another number then the result should formulate the difference amount. Hope that makes sense.
Great !!
where is the excel download to follow along?
I'm on a MacBook Pro using Excel and I'm using the if/and function and even though the value is true it is returning the false value. I've started a new worksheet and pasted all the data as values and I'm still having the same issue. Here is my formula"
=IF(AND(Table1[@Type]="Other fee",Table1[@Description]="Ad Fee Standard"), "Ad Fee Standard","")
Basically, Table1 has a 'Type' column in that column the word "Other fee" is there, also Table1 a 'Description' column in that column the word "Add Fee Standard" is there - so it should be returning the value Ad Fee Standard, but it is leaving the cell blank. I would welcome any insight, as I'm quite frustrated as to why this will not work.
What about when you want the If/OR function to define and use a constant for an equation?? (Division specifically)... I'm getting stuck.
If we have 3 columns with Schedule start date, Schedule end date and Actual end date.
Can we find the number of days between two dates based on text from a cell .. if text value is “resolved” I need the date formulas as current date minus scheduled end date. If status is “resolved late “ then Actual end date minus schedule end date.
Hi Jon, I saw you touched briefly on the VLOOKUP, I'm having issues sometimes with the VLOOKUP not being able to pick value sometimes even though there is an exact match with no invisible spaces or wrong characters, I wonder if you experienced that as well and what is causing it... The reason I know the target which I'm comparing is an exact match is because when I copy and paste those columns on a new sheet it all of a sudden pick up the value.... I also make sure the formatting is the same or clearing the formats. A similar issue happens on search function sometimes.
May be try to ensure that there are no spaces by using Text to columns under Data bar. It works for me when using vlookup
Hi Benny, are the values numbers or dates? If so, it could be a rounding or decimal place issue. Vlookup requires the values to match exactly, including decimals.
If the values are text then it will likely be a different issue.
We don’t have a public forum but we do have a community forum inside our Elevate Excel Training Program where members can ask questions and post files.
You can also submit a question and sample file at www.excelcampus.com/ask/
Due to the high volume we receive, we can’t promise to answer all questions submitted there but do consider them for future YT videos.
I hope that helps.
thank yo Jon Acampora
You're welcome, @mustafayusuf4112 😀
Thank you Jon! Is there an IF formula or is there another function that would allow me to look at how many times "a specific value" appeared and what dates were associated with it? For example: Let's say I have 10 Pickers on my farm and every day the total number of fruit picked are logged, by date, amount, type, and picker.
DATE (A) FRUIT (B) Picker ID (C) Amount Picked (D)
10/19/2021 (A) Apples (B) Picker 1 (C) 60 (D)
10/19/2021 (A) Apples (B) Picker 2 (C) 92 (D)
10/19/2021 (A) Oranges (B) Picker 3 (C) 90 (D)
10/19/2021 (A) Oranges (B) Picker 4 (C) 74 (D)
10/20/2021 (A) Oranges (B) Picker 1 (C) 60 (D)
10/20/2021 (A) Oranges (B) Picker 2 (C) 92 (D)
10/20/2021 (A) Apples (B) Picker 4 (C) 90 (D)
10/21/2021 (A) Apples (B) Picker 4 (C) 74 (D)
If I I wanted to know how many times the value "apples" occurred for each date adjacent to it within Column A (Date), how would I count how many times Column B (Fruit) returned the value for apples on that given date? I would like a return value that looks at all the dates within the date column where both Apples and a Date occur and count how many times it appears for that date. From my data snapshot above my ideal result would say:
10/19/21 2 (which means there is a total of 2 occurrences for apples on 10/19/21), and 10/20/20 1 (which means there is a total of 1 occurrence for apples on 10/20/21), and
10/21/20 1 (which means there is a total of 1 occurrence1 for apples on 10/21/21).
Any help you can offer is GREATLY appreciated!
What if I don't have a table? this won't work for non-table. What if I want to select a range from eg: H6 to H28 and want to show which ones are larger/smaller.
That's why when I tutor people, I always think of an alternative because people might not have the same information as me.
How to code a cell that I want two conditions. For example I want the cell to return 1 if
How to use if formula to provide numerical results of difference greater or higher than said amount?
I'm not sure I fully understand your question. Would you mind providing more explanation? Thanks!
Can someone direct me to a link that talks about two cells comparison? e.g. if A1=1 and if B1=2 then C1=2 and if A1=2 and if B1=1 then C1=1 and so on. I have four conditions for C1. How do I write all of them in one formula?
Greetings. I hope you can help me figure this out. I have numerous customers, all with various charges for our servicer. Is there a formula whereby I can set up a column that will automatically enter the amount charged to each customer when their name is entered into column A, then column B enters the amount we charge them? For example, Smith's Flower Shop pays $50.00 per service and Joe's Auto Shop pays $35.00 for the service we render to him. How can I set up a column that automatically enters the amount each customer pays? I hope that that makes sense 🙂. Thanks in advance for your help.
Hi there! 😊 One way is to use a VLOOKUP. Create a table in the same file but on another worksheet with servicers in the first column and known dollar amounts in the second column. For example, the newly created table is on Sheet1 and takes up Cells A1:B10. Then, in Column B of your original table, use a VLOOKUP function. If your first name is in Cell A2, then in Cell B2 enter something like:
=VLOOKUP(A2,Sheet1!A1:B10,2,FALSE)
That will take the servicer name from Column A ("A2")in the original table, look it up in the newly created table ("Sheet1!A1:B10"), find the corresponding dollar amount ("2" for the second column), and output the exact (FALSE) match to your original table into Column B. Hope that helps! 🙂
Hi, why this function dont working =IFS(S6S6>1801,"B", 2800>S6>2251,"C", 3100>S6>2801,"D",S6>3101,"E")
Instead of using two carats with x>Cell>y, try using one for each condition, such as x>Cell. For example, try something like IFS(S6<1800, "A", S6<2251, "B", S6<2801, "C", ...). Any number less than 1800 will be handled by the first condition ("A"), so any other numbers would have to be greater than or equal to 1800 and would be handled by the successive conditions.
HELLO JON,
I want two fill land type with column a , b , c , d. If A is greater then all 3 then type A or if B is greater then A, C and D then type B or if C is greater then A, B and D then Type C or if D is greater then A, B, C then type D in E2 cell.
pls help
Hi Neelesh, It sounds like you can use the MAX function for this. MAX will return the largest value in the range of numbers specified in the function.
I hope that helps. Thanks again and have a nice day! 🙂
@@ExcelCampus Hi JON,
Thanks for your response. I don't want to write largest value in the range. I have 3 columns with different number with title A, B, C AND D. I want to write title name instead of values.
If I understand correctly, You could combine the MAX function with a lookup function to lookup the max value and return the adjacent column. Something like the following.
=XLOOKUP(MAX(B1:B10),B1:B10,A1:A10)
I hope that helps. Not sure I fully understand the question.
How I find large 3 salary from a salary list
Couldn't get to that excel sheet.
I'm trying to use multiple IFs in a single cell. Not IFs nested inside of each other, but rather one IF followed by another and then another and so on. But everything I tried comes up with an error. What should I do?
Hi Burst! 😊 If you want to join the separate IF results together as a string of text, use the ampersand (&) symbol. You may want to separate the results with a space, slash, or pipe ( | ), which can be done like &" | "& in between the IF formulas. Hope that helps! 😊
@@ExcelCampus That worked, thanks.
@@ExcelCampus New question. The IFs I'm using have results that output blank text. I want to make it so that if every IF in the cell outputs blank text, the cell will show "None" instead of appearing blank. Is there a way to do that without nesting those IFs inside another IF?
8:01 Hi May I know the formula if same company and will total sum of same company amount in the new cell？
Hi Kahshen! If I understand correctly, you probably want to use the SUMIF or SUMIFS function for that. It will calculate the sum of a range based on the criteria in one or more ranges.
We'll add this to our list for future videos. Thanks! 🙂
I don't see the downloadable file.
Ok Never mind I found it hehe
@@laconan I did not see downloadable file too
I don't see the file
Hello! you can download the file from this link www.excelcampus.com/functions/how-to-write-if-function/ under "Download the Excel File" Thanks! 😀
@@ExcelCampusthis link is not working can you send the valid link
Thanks Jon :), i have an issue calculating total sales delivered by delivery truck, i used the sumifs formula but it gave the wrong answer! can I email you the spreadsheet?
thank you, Jon