I've been following your videos religiously and even taken several of your Udemy courses (right now in the dashboard course). You're an excellent teacher! This newly minted MOS Excel expert salutes you.
Explanation of how to use wildcards with ifs function is really amazing. It helped to deal with large data set with multiple criteria. This simple explanatory video really helped me a lot. Thank you for posting this video
Grüß Gott Leila, I know this is an old video but this is such a good tip rather than adding a helper column with a left formula. In the end, all roads lead to Rome but you definitively got the smartest shortcuts!!
Thanks Leila. All your videos are really useful and informative. I think your skills of explaining things precisely makes all your videos more relevant and valuable.
Hi Leila.. great tip and so logical. Before watching, I first tried something like this: =SUMPRODUCT(ISNUMBER((SEARCH(D1,A1:A3)))*B1:B3) with my criterion range in A1:A3, my sum range in B1:B3 and my criteria in D1. It works, but maybe is cumbersome to understand than SUMIFS() with its structured arguments. I never thought of using the wildcard characters to modify the criteria within SUMIFS(). Of course, that makes sense, now that you provided the example and opens new possibilities, as you demonstrated with LEFT and RIGHT. Excellent! Thanks and Thumbs up!
Guess you helped in building Excel. You are just too much! I wouldn't be surprised you would be introducing some new functions to to the Excel developers soon. You are awesome!
I thoroughly enjoy all of your videos. They are very clear and concise and have helped me solve a lot of problems and streamline a lot of processes at work. Thank you very much for the time and effort you put into this. It is very much appreciated.
Thanks to you, I could get the same results using SUMPRODUCT function. For example: we can get the sum quantity which contains AT, E6, 10, or 30_ by using this formula: =SUMPRODUCT(--ISNUMBER(SEARCH(F6,$A$4:$A$15)),$C$4:$C$15) and almost the same with next other conditions using SUMPRODUCT with LEFT and RIGHT functions. . Keep uploading the challenging excel videos :)
Thank you! This was very comprehensive and thourough! I was actually looking for the *NOT operator such that sum everything except if a cell has a string in it and it was easy to get there by simply adding the ** in front of your function segment. It may seem simple but without it being said or not being able to search on that it was difficult to find this video which was the EXACT video I needed!! THANK YOU so much!!
Excellent. Every video you are kindling an interest learn more & more tricks. Thanks a lot to my beloved/beautiful/brainy/awesome/witty Excel Teacher. You are making us MAD OF EXCEL. Really I love/like your videos very much.
Having an Excel exam tomorrow for new position at work. Looked up most of your videos, excellent job. Much appreciate it. Great refresher and few things i did not know about. Looking forward to more videos of yours. Thanks a mill. It's a sub !
@@LeilaGharani Easier then expected. Few nested IFs with ANDs and ORs, vlookup, basic math, just long formula, pivot chart with slicers and forecast model. Nailed it :) Waiting for official feedback, but pretty sure will be offered the possition. Thank You very much for all the videos. Helped me a lot.
Great stuff..especially the combination of left and right function added with customer combination.it took a bit of time but finally understood.thanks!
You are so so kicking ass with your tutorials. Never a dull moment when watching your skills, tips and tricks to improve. My hat´s off with a big thank you. Keep it up. :)
i guess im randomly asking but does any of you know a trick to log back into an instagram account..? I stupidly lost my account password. I would love any tips you can give me
@Adrien Sonny I really appreciate your reply. I got to the site through google and I'm in the hacking process atm. Takes quite some time so I will reply here later with my results.
Absolutely Outstanding! This is one of those prime issues in Excel - You have a glob of text and numbers in a many-rowed column spreadsheet, yet you need to sum up those costs are of a particular word(s) or number(s) are inside the chosen column. For instance: ashfkdhakljahdkjToolingjkjdd $3,000 janskjfnkajsdToolingddasdfc $20,000 adfodiajfoiadjfoidajfifjdoafo $15,000 fkdkfjkjToolingkmnjnjnjknkj $100,000 What are all the tooling costs for the Purhase Orders we have done?
Can you do this with multiple parameters that would output different words if TRUE? I have 5 different codes that need to have a proper name in another column. Each code has the same 3 letters that is equal to a proper name, but the three letters vary in what position they hold.
Brief, analytic and right on the spot as always Leila. Thank you for your videos. I have a question, and though it is not very related to this video, I thought of placing it here due to the use of wildcards. It is about the CTRL + F command. So here goes; Let's say that I need to exclude cells that have particular added values, while I need to find all the others that don't have them. Example; I have 5 cells that contain the word "Bread" and 5 more that contain the words "White Bread". Let's say that I need the "Find" command to show me only the cells with the "Bread" value. If I hit CTRL + F and type "Bread" it will return all 10 cells. Is there a wildcard which will do the opposite of "*"? So that if I place it in front and at the end of the letter/letters/word that I need excel to exclude from the search, it will comply? For example, let's say that this wildcard exists and it is the "#" symbol. If I hit CTRL + F and type "#Wh#Bread" it will give me only the 5 cells with just the "Bread" value? Also if you select multiple cells by holding down the CTRL key, is there a way to deselect a mistakenly selected cell, without having to repeat the whole process again from the beginning? Thanks in advance. Stay Safe.
You are good at explaining the ins and outs of Excel. I'm curious about your first language; is it Hindi? Your English is very good. Keep up the great videos.
Hi Leila, Great lecture Is there a way to use wildcards and sumifs when your criteria range are numbers? lets say to add amounts for all the 4000s account numbers? (I tried 4&"*" as my criteria but didn't work)
You are a great personality... There are more than one million videos about excel.they are simple but your videos is fantastic and advanced level. This thing make you unique youtuber. If you provide this videos in hindi language so I can understand better way. Please maam.....
Grab the file I used in the video from here 👉 pages.xelplus.com/sum-partial-match-file
I've been following your videos religiously and even taken several of your Udemy courses (right now in the dashboard course).
You're an excellent teacher! This newly minted MOS Excel expert salutes you.
Glad you like the videos. Many thanks for your support of my courses.
Wow !!!! you are Guru, i am admired, inspired and impressed by your tutorials ,,,, thanks for sharing such knowledgeable material...
It's my pleasure!
Explanation of how to use wildcards with ifs function is really amazing. It helped to deal with large data set with multiple criteria. This simple explanatory video really helped me a lot. Thank you for posting this video
Grüß Gott Leila, I know this is an old video but this is such a good tip rather than adding a helper column with a left formula. In the end, all roads lead to Rome but you definitively got the smartest shortcuts!!
Dankeschön 🤗
Excellent Leila even though I'm quite advanced in excel there is always something to learn and this is the place to do it..😊
True Fransen!
all your lessons are great contribution to the world! Thks so much
Thanks for the kind words. Glad you like the tutorials.
This is a great solution to a problem I had... I can't believe a simple wildcard is the answer.
Thank you Leila.
Thanks Leila. All your videos are really useful and informative. I think your skills of explaining things precisely makes all your videos more relevant and valuable.
Thank you very much Atul! I'm glad you find the videos easy to follow & relevant. Thank you for your support :)
Hi Leila.. great tip and so logical. Before watching, I first tried something like this: =SUMPRODUCT(ISNUMBER((SEARCH(D1,A1:A3)))*B1:B3) with my criterion range in A1:A3, my sum range in B1:B3 and my criteria in D1. It works, but maybe is cumbersome to understand than SUMIFS() with its structured arguments. I never thought of using the wildcard characters to modify the criteria within SUMIFS(). Of course, that makes sense, now that you provided the example and opens new possibilities, as you demonstrated with LEFT and RIGHT. Excellent! Thanks and Thumbs up!
There is no single solution. Just have to use what you feel most comfortable with. Many thanks for your input Wayne!
I think from a practical perspective, this has to be one of your best videos. Fantastic. Thank you very much.
Guess you helped in building Excel. You are just too much! I wouldn't be surprised you would be introducing some new functions to to the Excel developers soon. You are awesome!
Excellent. I always get solutions from your channel regarding Excel.
I'm glad to hear that :)
I thoroughly enjoy all of your videos. They are very clear and concise and have helped me solve a lot of problems and streamline a lot of processes at work. Thank you very much for the time and effort you put into this. It is very much appreciated.
You're very welcome Rich. Thank you for the kind feedback!
You are so awesome Leila! You make everything so easy and simple with your wonderful teaching. Thanks so much as always! God bless you for your gift!
Thank you so much Tom!
Thanks to you, I could get the same results using SUMPRODUCT function.
For example: we can get the sum quantity which contains AT, E6, 10, or 30_ by using this formula:
=SUMPRODUCT(--ISNUMBER(SEARCH(F6,$A$4:$A$15)),$C$4:$C$15)
and almost the same with next other conditions using SUMPRODUCT with LEFT and RIGHT functions.
.
Keep uploading the challenging excel videos :)
Great job Leila and by-the-way you helped me pass my Excel Exam, thank you so much.
That's GREAT! Congratulations! You did the hard work yourself :)
Wonderful, perfectly explained, no time waisted, and so so helpful, tanks very much
Very useful and it was so handy when I needed it most
Thank you! This was very comprehensive and thourough! I was actually looking for the *NOT operator such that sum everything except if a cell has a string in it and it was easy to get there by simply adding the ** in front of your function segment. It may seem simple but without it being said or not being able to search on that it was difficult to find this video which was the EXACT video I needed!! THANK YOU so much!!
It was simple, clear and rich, god please you .
Glad you like it.
Ma'am I enjoyed This video ,I only want to say u made all thinks clear very easily. So thanks ma'am and pls keep making video for your "EXCEL FAN"😘.
I'm very glad to hear that. I will do my best to keep my Excel fans happy :)
Excellent. Every video you are kindling an interest learn more & more tricks. Thanks a lot to my beloved/beautiful/brainy/awesome/witty Excel Teacher. You are making us MAD OF EXCEL. Really I love/like your videos very much.
your teaching style is so kick-ass....love you
Great video. Very easy to follow. Bravo!
Thankyou leila.
Amazing video on sumifs.
Having an Excel exam tomorrow for new position at work. Looked up most of your videos, excellent job. Much appreciate it. Great refresher and few things i did not know about. Looking forward to more videos of yours. Thanks a mill. It's a sub !
How was your test?
@@LeilaGharani Easier then expected. Few nested IFs with ANDs and ORs, vlookup, basic math, just long formula, pivot chart with slicers and forecast model. Nailed it :) Waiting for official feedback, but pretty sure will be offered the possition. Thank You very much for all the videos. Helped me a lot.
Thanks Leila you are always the best excel lecturer ever..
Thanks for the kind words Ulrich!
Çok teşekkürler, harika bir anlatım. Türkiye'den selamlar.
You're very welcome. Greetings to Turkey!
Thank you so much 🥰 mam.
I found this from last an hour
No doubt, This is the best video on Advance excel sumifs function.
Thanks Hasnain :)
I'm a big fan of you...I found the "*"&... that is fit to my job.
Thanks a lot.
very very knowledge gain tutorial. i got it now. love it very...keep up it..thanku so much for this video.
Glad to help :)
Just come across this video. Brilliant, Leila!! Never realised it was possible to do this. Will solve so many problems.
I'm glad you found this helpful Henry.
I had this problem with countifs in the past. Now it works. Thank you.
Great it works now :)
This was exactly what I've been looking for for ages! This was a great help. Thank you kindly, Leila.
Glad it was helpful, Mia!
Thank you very much for detailed explanation.... really helped me and saved my time on figuring out for certain caracters 😘😘😘😘
Leila, you are awesome! This has just upped my ‘SumIf’ game by tons!
I'm glad to hear that! Thank you for your support of my tutorials.
It seems very good and useful knowledge which I wasn't aware earlier
You saved me from 4 days of work. Thank you so much
Glad I could help!
Thank you Leila - I do love the way you take a very valuable and usable function and explain it in such great context! Well done.
Thank you Gerald for your kind comment. I'm very glad you find value in the tutorials.
Great stuff..especially the combination of left and right function added with customer combination.it took a bit of time but finally understood.thanks!
I'm glad you find that useful. Agree - it can be a bit tricky though...
This is what exactly I was looking for.. thanks
You are so so kicking ass with your tutorials. Never a dull moment when watching your skills, tips and tricks to improve. My hat´s off with a big thank you. Keep it up. :)
i guess im randomly asking but does any of you know a trick to log back into an instagram account..?
I stupidly lost my account password. I would love any tips you can give me
@Krew Peter instablaster ;)
@Adrien Sonny I really appreciate your reply. I got to the site through google and I'm in the hacking process atm.
Takes quite some time so I will reply here later with my results.
@Adrien Sonny It did the trick and I actually got access to my account again. I am so happy:D
Thank you so much, you saved my account :D
@Krew Peter no problem =)
THANK YOU!!! I had been searching for a tutorial to do exactly this for a while!!
Absolutely astounding knowledge and wisdom of MS Excel
Thanks for your efforts, another awesome video
You are brilliant Leila
Very Nice refresh of using wildCard :-) thanks Leila :-)
You're very welcome Mohamed :)
It's too useful stuff. I wonder how could you find out this much numbers of simple, easily understandable examples to convey the ideas. Well done
you saved my life, this is great information, i feel i just unloked a new skill in Excel :D thanks
Awesome video. This will be very helpful with my career!
I'm glad it's helpful Bob!
I finally have solved my problem. Thank you very much!!
You're welcome!
I always learn watching your videos. I didn't think to use the left and right etc.. many thanks.
Very well explained! Thank you
This is so Awesome, I was confused and your this video helped me submitting my report to Line Manager on time. :) Thumbs up!!!!!
This video is absolutely help me a lot.. thanks!
Glad to hear it!
Thanks for sharing such a valuable information.
You're very welcome Ashok!
Hi Leila,
Your videos never fail to impress the viewers. Very useful :)
thank god. I would NEVER have figured out the "*"&G3&"*" syntax
Dear, u r great I think it should be helpful example for future. Thanks
And u r looking gorgeous 😊
Thank you! This helped me a lot!
Very useful video.Thank you Leila!
You're very welcome. Thank you Chaminda for dropping by :)
Thank you very much Leila.... for the selfless services.... wish you and your family a very Happy and Prosperous New year 2019..... from India!!!
You're very welcome. Hope you have a wonderful 2019 as well!
Thank you very much , it helped me a lot
Very nicely explained
This video came along at exactly the right time for me, so thank you very much!
Perfect! You're very welcome :)
This solves my heart-burns recently. Thank you Leila!
I'm glad I could help with that :)
Awesome!! I really improved my excel skills with the knowledge shared by you. Thanka Leila.
Absolutely Outstanding!
This is one of those prime issues in Excel - You have a glob of text and numbers in a many-rowed column spreadsheet, yet you need to sum up those costs are of a particular word(s) or number(s) are inside the chosen column.
For instance:
ashfkdhakljahdkjToolingjkjdd $3,000
janskjfnkajsdToolingddasdfc $20,000
adfodiajfoiadjfoidajfifjdoafo $15,000
fkdkfjkjToolingkmnjnjnjknkj $100,000
What are all the tooling costs for the Purhase Orders we have done?
Very useful indeed. Thanks a lot.
Yes, I m using this * trick to find something in excel but you teach me this can also apply in formula.
Thanks 😊
That's great :)
Very good video. Thank you.
thank you very much!!! it's worked
Absolutely the best!
Thank you so much Leila excellent and useful tutorial nice voice. God bless you.
Is there a way to sum two IDs? Example: I would like to have AT & E6 sales calculated together.
Thank you so much, was stuck on that one today for a bit
Can you do this with multiple parameters that would output different words if TRUE? I have 5 different codes that need to have a proper name in another column. Each code has the same 3 letters that is equal to a proper name, but the three letters vary in what position they hold.
Brief, analytic and right on the spot as always Leila. Thank you for your videos.
I have a question, and though it is not very related to this video, I thought of placing it here due to the use of wildcards. It is about the CTRL + F command. So here goes;
Let's say that I need to exclude cells that have particular added values, while I need to find all the others that don't have them. Example;
I have 5 cells that contain the word "Bread" and 5 more that contain the words "White Bread". Let's say that I need the "Find" command to show me only the cells with the "Bread" value. If I hit CTRL + F and type "Bread" it will return all 10 cells. Is there a wildcard which will do the opposite of "*"? So that if I place it in front and at the end of the letter/letters/word that I need excel to exclude from the search, it will comply? For example, let's say that this wildcard exists and it is the "#" symbol. If I hit CTRL + F and type "#Wh#Bread" it will give me only the 5 cells with just the "Bread" value?
Also if you select multiple cells by holding down the CTRL key, is there a way to deselect a mistakenly selected cell, without having to repeat the whole process again from the beginning?
Thanks in advance. Stay Safe.
Hi, you are amazing. Thank you!!!!
Good job Leila...
Glad you like it!
You are good at explaining the ins and outs of Excel. I'm curious about your first language; is it Hindi? Your English is very good. Keep up the great videos.
Thank you so much ❤
Nice teaching you are very good....
Hi Leila, Great lecture
Is there a way to use wildcards and sumifs when your criteria range are numbers? lets say to add amounts for all the 4000s account numbers? (I tried 4&"*" as my criteria but didn't work)
Very useful.. wildcard thing was new for me .
Really fantastic. ........LG
Glad you like it.
It's so helpful to me. Thank you!
Lovely tutorial. Thank you for the help.
You're very welcome!
wonderful trick
Very Useful..Thanks for such great Videos.
Just wow.. Thanks.
You make it so simple. Gr8
THANK YOU MY LOVE
Thanks for the wildcards video :)
Great and easy to understand.
Impressive thank you for sharing your skills.
You are a great personality...
There are more than one million videos about excel.they are simple but your videos is fantastic and advanced level. This thing make you unique youtuber.
If you provide this videos in hindi language so I can understand better way. Please maam.....
Thank you Ahmad for the kind words....Unfortunately I don't speak hindi :(
Really useful stuff, thank you!
This is really good! Thank You.
Glad you find it helpful Joshua!