How to Use SUMIFS with Partial Match and Wildcards in Excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/sum-partial-match-file

[@marcvslicinivscrassvs7536]

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.

[@LeilaGharani]

Glad you like the videos. Many thanks for your support of my courses.

[@ravishekharsingh178]

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

[@ericsn6158]

This is a great solution to a problem I had... I can't believe a simple wildcard is the answer. Thank you Leila.

[@m.raedallulu4166]

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 :)

[@edot2068]

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!

[@TomBsChannel]

I think from a practical perspective, this has to be one of your best videos. Fantastic. Thank you very much.

[@wayneedmondson1065]

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!

[@LeilaGharani]

There is no single solution. Just have to use what you feel most comfortable with. Many thanks for your input Wayne!

[@Cmdr_Amethyst]

Leila you really do deliver master class-tier content for Excel, and I find myself *uniformly* inspired to do better through watching your videos. You treat the audience with respect, and it is so very much appreciated. Thank you for being an amazing content creator =]

[@LeilaGharani]

Thank you so much for the kind feedback!

[@atulkabra]

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.

[@LeilaGharani]

Thank you very much Atul! I'm glad you find the videos easy to follow & relevant. Thank you for your support :)

[@martijnfransen9933]

Excellent Leila even though I'm quite advanced in excel there is always something to learn and this is the place to do it..😊

[@thakursamveeth]

True Fransen!

[@kenburns5626]

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!!

[@tanveerhussain79]

your teaching style is so kick-ass....love you

[@richknoll7152]

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.

[@LeilaGharani]

You're very welcome Rich. Thank you for the kind feedback!

[@henryg5735]

Just come across this video. Brilliant, Leila!! Never realised it was possible to do this. Will solve so many problems.

[@LeilaGharani]

I'm glad you found this helpful Henry.

[@samantablur5924]

Wonderful, perfectly explained, no time waisted, and so so helpful, tanks very much

[@tomnicolle195]

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!

[@LeilaGharani]

Thank you so much Tom!

[@shevshenko1432]

Great job Leila and by-the-way you helped me pass my Excel Exam, thank you so much.

[@LeilaGharani]

That's GREAT! Congratulations! You did the hard work yourself :)

[@1gopalakrishnarao]

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.

[@tomaszsamsel7710]

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]

How was your test?

[@tomaszsamsel7710]

@@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.

[@oxanamelnic1241]

Thank you very much for detailed explanation.... really helped me and saved my time on figuring out for certain caracters 😘😘😘😘

[@vikasdsem9914]

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"😘.

[@LeilaGharani]

I'm very glad to hear that. I will do my best to keep my Excel fans happy :)

[@hasnainak4252]

No doubt, This is the best video on Advance excel sumifs function.

[@LeilaGharani]

Thanks Hasnain :)

[@seizuresaladventures]

Leila, you are awesome! This has just upped my ‘SumIf’ game by tons!

[@LeilaGharani]

I'm glad to hear that! Thank you for your support of my tutorials.

[@miar4838]

This was exactly what I've been looking for for ages! This was a great help. Thank you kindly, Leila.

[@LeilaGharani]

Glad it was helpful, Mia!

[@mattias5063]

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. :)

[@krewpeter1436]

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

[@adriensonny9741]

@Krew Peter instablaster ;)

[@krewpeter1436]

@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.

[@krewpeter1436]

@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

[@adriensonny9741]

@Krew Peter no problem =)

[@haroonrasheed2212]

Wow !!!! you are Guru, i am admired, inspired and impressed by your tutorials ,,,, thanks for sharing such knowledgeable material...

[@LeilaGharani]

It's my pleasure!

[@lofhof]

I had this problem with countifs in the past. Now it works. Thank you.

[@LeilaGharani]

Great it works now :)

[@knickandknac]

THANK YOU!!! I had been searching for a tutorial to do exactly this for a while!!

[@MrsCathT]

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!!

[@LeilaGharani]

Dankeschön 🤗

[@ulrichboussouka540]

Thanks Leila you are always the best excel lecturer ever..

[@LeilaGharani]

Thanks for the kind words Ulrich!

[@kearo]

you saved my life, this is great information, i feel i just unloked a new skill in Excel :D thanks

[@geraldpdavid1]

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.

[@LeilaGharani]

Thank you Gerald for your kind comment. I'm very glad you find value in the tutorials.

[@zahirpaki]

Excellent. I always get solutions from your channel regarding Excel.

[@LeilaGharani]

I'm glad to hear that :)

[@jadeking1]

This is so Awesome, I was confused and your this video helped me submitting my report to Line Manager on time. :) Thumbs up!!!!!

[@ml2006ggg]

I always learn watching your videos. I didn't think to use the left and right etc.. many thanks.

[@saiphenay]

all your lessons are great contribution to the world! Thks so much

[@LeilaGharani]

Thanks for the kind words. Glad you like the tutorials.

[@imranmushtaq7445]

Absolutely astounding knowledge and wisdom of MS Excel

[@VS-rh8rq]

Great stuff..especially the combination of left and right function added with customer combination.it took a bit of time but finally understood.thanks!

[@LeilaGharani]

I'm glad you find that useful. Agree - it can be a bit tricky though...

[@mounikashamala4309]

You saved me from 4 days of work. Thank you so much

[@LeilaGharani]

Glad I could help!

[@JohnYoga]

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?

[@slslrilil]

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

[@sachinrv1]

Hi Leila, Your videos never fail to impress the viewers. Very useful :)

[@RaviSingh-wm4gm]

Thankyou leila. Amazing video on sumifs.

[@lawtutor-leng]

I'm a big fan of you...I found the "*"&... that is fit to my job. Thanks a lot.

[@osamasalah341]

Thanks for your efforts, another awesome video You are brilliant Leila

[@srinivasank2990]

Very useful and it was so handy when I needed it most

[@mrawaan5990]

Great video. Very easy to follow. Bravo!

[@divyanshkhorwal3704]

Thank you so much 🥰 mam. I found this from last an hour

[@lww8132]

This solves my heart-burns recently. Thank you Leila!

[@LeilaGharani]

I'm glad I could help with that :)

[@tha2irtalib343]

It was simple, clear and rich, god please you .

[@LeilaGharani]

Glad you like it.

[@rahilkazi1134]

Awesome!! I really improved my excel skills with the knowledge shared by you. Thanka Leila.

[@madhavneupane168]

very very knowledge gain tutorial. i got it now. love it very...keep up it..thanku so much for this video.

[@LeilaGharani]

Glad to help :)

[@DonovanJenkins]

This video came along at exactly the right time for me, so thank you very much!

[@LeilaGharani]

Perfect! You're very welcome :)

[@mohamedchakroun4973]

Very Nice refresh of using wildCard :-) thanks Leila :-)

[@LeilaGharani]

You're very welcome Mohamed :)

[@Ozibar]

Çok teşekkürler, harika bir anlatım. Türkiye'den selamlar.

[@LeilaGharani]

You're very welcome. Greetings to Turkey!

[@satishgopal2406]

Thank you very much Leila.... for the selfless services.... wish you and your family a very Happy and Prosperous New year 2019..... from India!!!

[@LeilaGharani]

You're very welcome. Hope you have a wonderful 2019 as well!

[@AshokKumar-sy2qt]

It seems very good and useful knowledge which I wasn't aware earlier

[@mera_bharat_mahan5171]

This is what exactly I was looking for.. thanks

[@DannyPops]

thank god. I would NEVER have figured out the "*"&G3&"*" syntax

[@erdemtatar6554]

very very helpful. thank you

[@sirphilipgreen]

Very well explained! Thank you

[@bobtheauditor08]

Awesome video. This will be very helpful with my career!

[@LeilaGharani]

I'm glad it's helpful Bob!

[@chamindabasnayake4844]

Very useful video.Thank you Leila!

[@LeilaGharani]

You're very welcome. Thank you Chaminda for dropping by :)

[@rjsdjvh1]

I finally have solved my problem. Thank you very much!!

[@LeilaGharani]

You're welcome!

[@AshokKumar-sy2qt]

Thanks for sharing such a valuable information.

[@LeilaGharani]

You're very welcome Ashok!

[@hemendrakumarvarshney8825]

Very nicely explained

[@dancingqueen0728]

Thank you! This helped me a lot!

[@caturdimashartadi8658]

This video is absolutely help me a lot.. thanks!

[@LeilaGharani]

Glad to hear it!

[@MalinaC]

Thanks for the wildcards video :)

[@LeslieSmith-h6p]

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.

[@ArielPalmer534]

Is there a way to sum two IDs? Example: I would like to have AT & E6 sales calculated together.

[@educacionportable]

Thank you so much Leila excellent and useful tutorial nice voice. God bless you.

[@akhu3034]

What did you press at 7:56 from H23 to H26

[@WeeMan277]

Thank you so much, was stuck on that one today for a bit

[@VijayKumar-zd7ou]

Dear, u r great I think it should be helpful example for future. Thanks

[@VijayKumar-zd7ou]

And u r looking gorgeous 😊

[@anthonylouis6874]

what about automatic day and hour stamp of google forms... ex. 01/01/2020 10:20:48 // they come in this format, im trying to use something like =sumif($A$2:$A$9;"01*";$I$2:$I$9) in google spreadsheets (the rule has different sum range, but anyway) the problems seems to be the "01*" to match when compared the timestamp auto generated by google forms 01/01/2020 10:20:48. Im trying to sum values when I have the same day.. but always returns 0, even when the I colums values are real... I also tryied "01"&"*", and also some different formats.... Im starting to think thats my problem is about the auto time stamp format that cant be compared in normal ways to the texts like "01*"

[@sarfarajkureshi6569]

Nice teaching you are very good....

[@Hari-n4f]

Very useful indeed. Thanks a lot.

[@hakanolmez4776]

Hi Leila; if company ID is numeric value what we need to do?

[@JUHILLAPSIWALA007]

Yes, I m using this * trick to find something in excel but you teach me this can also apply in formula. Thanks 😊

[@LeilaGharani]

That's great :)

[@jaipalsingh1416]

Thank you very much , it helped me a lot

[@kevinbedell9491]

Absolutely the best!

[@rockguitarist8907]

Very good video. Thank you.

[@vlogging-nk]

Lovely tutorial. Thank you for the help.

[@LeilaGharani]

You're very welcome!

[@oasispark5359]

It's so helpful to me. Thank you!

[@robbieknott6901]

Hi, you are amazing. Thank you!!!!

[@vdpeer]

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.

[@vikascma]

Very Useful..Thanks for such great Videos.

[@hereg3043]

Great and easy to understand.

[@krishnakumarnataraj9045]

Good job Leila...

[@LeilaGharani]

Glad you like it!

[@NimaRussell]

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)

[@sushantjoshi5865]

Very useful.. wildcard thing was new for me .

[@kweysi]

You make it so simple. Gr8

[@Page157]

thank you very much!!! it's worked

[@elit6366]

Thank you so much ❤

[@dineshdeshwal7685]

Really fantastic. ........LG

[@LeilaGharani]

Glad you like it.

[@awesh1986]

As usual awesome tricks.

[@Dev_Bartwal]

Good job LG💕

[@ckokse]

Really useful stuff, thank you!