How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)

[@LeilaGharani]

Grab the file I used in the video from here: pages.xelplus.com/sumifs-file

[@vichu847468]

@leilagharani Kindly help me with this problem. My boss is eating on creating an analysis table. The call time is eg. 8:24:55 AM and 5:44:67 PM, I need only the hour from here. But hour function is used but for pm it is showing in 24 hours format. But they want in number format only. How can I do it. The formula mention in the shet was numbervalue.. I'm not getting it with that formula please help..

[@aiccertifiedaccountants]

@@vichu847468 Use the text function like this "TEXT(A1,"hhh")" and it will return the hour in number format.

[@vanesacova8681]

Leila you have saved me so many tears these past months. I´ve got a task from my boss to make a 5 year-budget for the whole department in EXCEL and your tutorials have just helped so much!

[@LeilaGharani]

I'm happy to hear that Vanesa!

[@SK_3231]

@@LeilaGharani ok

[@ninaayco2326]

Thanks

[@folkkuff]

I was here trying to learn SUMIFS but turned out I learned; - AVERAGEIFS - COUNTIF - COUNTIFS - how to put > or < and " into the formula interchangeably - and how to transform data into an Excel table with ease I don't get why a hundred people gave her dislikes but this sure is one of the best Excel how-to video!!

[@thedr00]

I've been struggling with SUMIF(s) all morning, this sorted my issue out immediately, thank you for sharing your knowledge!

[@LeilaGharani]

I'm glad it was helpful!

[@mildredsouza5907]

Hi Leena , Please can you tell my how can i count a particular text in a column excluding certain rows , eg rows s2:s500 and count rows that say on track excluding text in row s4 / s13/s18

[@RobinEdayanal]

Mee too

[@KC_47.]

@@mildredsouza5907 how about if while you use the formula, you press CTRL and count it individually 1 by 1.. I mean some kind like this =countif((s2:s12)+countif(s14:s17)+countif(s19:s500))

[@giftnew2998]

@@mildredsouza5907 i found farmula better than countifs watch and decide may its help you kzbin.info/www/bejne/i6iYfpx7eqaSaaM

[@atulchauhan1504]

OMG, you are so good at explaining. I prepare a monthly report for my client using pivots. I stopped using pivots. Now I use SUMIF. Thank you so much.

[@ceeroar]

Leila, you are truly an inspiration to us all. Your excellent Excel videos saved my career. I cannot adequately thank you enough for everything. I am beyond forever grateful to you for the lecture videos. Looking forward to your future videos that are ALWAYS easy to understand.

[@LeilaGharani]

I'm really glad you like the videos!

[@StewartHarding]

I have a strong understanding of basic spreadsheet formulas, but I've been searching all day today for a formula which will allow me (as a stock market trader) to calculate the average spread (the difference between an asset's sell price and its buy price at the time of purchase), but ONLY with regards to my trades which have ended up being profitable and then I wanted another average spread calculation for trades which were NOT profitable, so which factors in the binary 1's and 0's in my profit and loss columns. You've just solved this problem for me and taught me something extra about spreadsheets. Thank you!

[@LeilaGharani]

You're very welcome! I hope a lot was in the profitable column :)

[@kebincui]

Excellent tutor, you explain fumula so clearly thank you Leila!

[@marcioyamawaki]

Thanks a lot Leila, I liked your explanation very much, specially the reason why we shall not use SUMIF anymore but SUMIFS and the TABLES explanation at the end. Regards from Brazil !

[@LeilaGharani]

Glad you like it Marcio.

[@excelisfun]

Great video! SUMIFS and the like are so powerful!!! And fun too : )

[@LeilaGharani]

So True! Thank you Mike.

[@hanyfouda3249]

Thanks alot I was having too many problems with SUMIFS , now every thing is more than OK , Thanks

[@LeilaGharani]

Glad to help.

[@hanyfouda3249]

@@LeilaGharani Thanks really you make Excel for me clear and simple

[@Xrayankhan]

you saved me ill watch all the rest your way is simple to understand

[@syednayabhaider1025]

Though Leila's videos are here on KZbin for years now but I noticed those only today. I don't know how many characters this KZbin comment area allows so I can endorse her work with the words those she deserves. I'm in the software field for the last 30 years but haven't seen such a tutor and neither I bothered to go out and start appreciating someone in this comment area. She is The Teacher, The Mentor, and let me say The Lady :) of course. Just Superb. Well done Leila!

[@LeilaGharani]

Wow, thank you very much for the feedback. Makes me very happy :)

[@jerryjulius6664]

Excellent tutor, made things very simple and easy, thank you!

[@LeilaGharani]

Very happy to hear that Jerry. You are very welcome.

[@ry8729]

I'm a relative novice to using Excel, and I find these functions to be a bit intimidating to master, HOWEVER, even I find this tutorial quite easy to understand and digest. I just became your subscriber. Thanks for this!

[@LeilaGharani]

That's great to hear. Thanks for the support. Agree - Excel functions are intimidating at first but they are lots of fun when you get to know them better :)

[@ry8729]

BTW, technically speaking, "Find out how many shirts were sold for the below criteria:" is incorrect, right? Because you're not trying to figure out the actual amount of shirts that were sold.

[@LeilaGharani]

Hi - actually yes - we need to count how many were sold here...

[@shivamanhas3399]

Thanks Mam for sharing ur knowledge of Excel with us so we can too Excel in Excel.Thaks a lot.... and keep sharing....

[@LeilaGharani]

You're very welcome Shiva. It's my pleasure. Thank you for your nice comment & your support.

[@rizalfatoni4075]

Come here looking for sumifs, turned out getting a lot more of useful information about excel. Thank a lot Leila, Good Luck

[@LeilaGharani]

Glad it was helpful!

[@shafeektb]

Hi Leila, really I am a fan of your videos and techniques, and keep watching...thank you

[@TheAriaBlake]

Wow! I went from using: =SUMPRODUCT((LEN(B2:31)-LEN (SUBSTITUTE ((B2:B31),K1,"")))/LEN (K1)) to using: =COUNTIFS(B2:B31,"*"&K1&"*") I was also able to start using * for many other equations! Thanks so much for this informative video!

[@LeilaGharani]

Great job!

[@hattemghanoom9557]

Thank you Leila Very smart & useful Excel techniques.

[@LeilaGharani]

You're very welcome Hattem :)

[@mdmoner484]

Hattem

[@ahmedmamdohcom]

Thanks for this value information

[@nelly5958]

This is a great refresher! Thank you, Leila!

[@Hey_Delight]

I've watched most of your videos if not all... I owe a lot my knowledge in making financial performance reports of my clients not just faster to accomplished but most importantly in quality outputs. You are one of my Excel mentors and I will forever be grateful of that. Again thank YOU so much...to you, other brilliant excel tutors and to KZbin for this new platform of learning that is truly beneficial to people around the world. You've given us an access to the technology and innovations not just applicable to finance, accounting and business people but so many other important learning tools making complicated things and sitiations not just solved but solved faster. More power Ms. Leila.

[@LeilaGharani]

Wow, I really appreciate the kind feedback. It's so great to hear that you could take this knowledge to improve your work. Thanks for the support and keep learning :)

[@Hey_Delight]

@@LeilaGharani ❤❤❤Thank You!!!❤❤❤

[@susans6794]

THANK YOU

[@LeilaGharani]

You're very welcome.

[@pauls7056]

Leila, you are a genius. I have used spreadsheets for years but in a really basic way - sum columns, subtotals etc. I started using MacNumbers and hit precisely the problem you showed: how do I populate cells with the results of several criteria. I could on the Sumif and that saved time. But I was trying functions like "AND" and could not get it to work. Then I wondered what the difference was between Sumif and Sumifs. I couldn't find anything (for MacNumbers) on it. Then I watched your video and kapow! The Mac worked in exactly the same way. Gone are the time consuming days of adding columns and transferring the results to a cell. Now, thanks to you, I can create a table how I want it and use Sumifs to populate the cells. Here's a virtual kiss on the hand and a big THANK YOU. Liked and subscribed.

[@s.b.483]

i don't trust people who can work with excel

[@ImageDivaMedia]

😅lol, that can lead to a world debate.

[@cleamahon4277]

Oh my gosh I've started a new job and I need to learn it, alongside everything else to learn...I'm so intimidated and they can sense I suck already 😅

[@salmankhalid5852]

Then consider yourself an idiot person. 😂

[@ahsenimtiaz1737]

​@cleamahon4277 I'm preparing for a final exam & I've just now started to get the hang of this....I think? 😅

[@user-jh5uj5rd7n]

Well we being forced to learn 😂😂

[@natashasingh8151]

GOT AN "A" IN MY MIST CLASS BASED PRIMARILY ON EXCEL....I HAVE NO ONE BUT YOU TO THANK. THANKS A MILLION LEILA YOUR VIDEOS ARE A BLESSING FOR STUDENTS LIKE US

[@LeilaGharani]

That's great Natasha. Congratulations!

[@jigneshjpatel33]

Very Nice illustrated, very fast and simply understandable ... Good Madam... Thank you very much ..

[@LeilaGharani]

You are most welcome

[@gajanandhumal8883]

Thank you...Excellent Tutorial. Namaste from India.

[@RoseaNebulaLaeta]

Your videos are so amazing! Every time you teach me a new function and show how fast excel can get the results - I have to pause the video because I'm just stunned! Wow! I feel like I'm watching a wizard reveal her deepest secrets

[@nareshkumarpenumetcha5953]

Thanks Ms Leila. These videos are very useful. Your style of tutoring is superb. To the point. Well designed examples. Namastae.

[@LeilaGharani]

Glad it was helpful!

[@prasannaprasanna9955]

Many videos but this gave me complete all details...................Your teaching is giving me idea of exploring functions in different ways Thanks

[@softambientmusic]

You have a gift! You make the seemingly complicated, remarkably simple. Now demystified Sumifs, countifs, Averageifs for all of us. Thanks Leila!

[@LeilaGharani]

You're very welcome. Many thanks for the kind feedback!

[@anantjoshi7983]

Excel formulas are hard to understand, but you explained it really good Thank you mam.

[@LeilaGharani]

Glad to hear that

[@zahamdani]

Very soft and polite way of teaching is the best tool for her. Nice and excellent videos on the other topics too.

[@LeilaGharani]

Thank you! Glad you like the videos :)

[@zahamdani]

You are most welcome. You deserve to be appreciated for your work. It is free for learners. Keep going.

[@emilycestari]

Leila, thank you so much! My team and I are building a histogram for a cruise line company’s spare parts list and we needed to find out how many vessels would receive the parts they ordered within a range of timeframes. The COUNTIFS function helped me do that easily and efficiently!

[@nickadams2924]

Leila, thank you for making my work easier and more productive! You deliver the information quickly and it is so much more information than expected!

[@maungatilimamichelo2995]

Wow, i have struggled to understand this formulars and you have made so easy for me to understand. Thanks for sharing.

[@LeilaGharani]

Glad it was helpful!

[@maungatilimamichelo2995]

@@LeilaGharani , am searching for more of your videos now. thanks again.

[@georgelh]

Excellent Videos. Very thorough and complete!

[@LeilaGharani]

Much appreciated, George!

[@gildatashakori4877]

Great Video. Helped me to fix my spreadsheet error.

[@riteshgangwani115]

Thanks for spreading knowledge

[@aashrayagarwal4631]

Very useful for developing habit of reducing manual work and automate task. Thanks Leila. Expecting more from you.

[@LeilaGharani]

You are welcome 😊

[@kalpeshkothari656]

Explain very thoroughly- its too good

[@muralikrishna-mi1nh]

It's a nice and very useful video.your teaching is very good .thank You madam

[@hamadal-shammari253]

Thank you for excellent easy explanation

[@kamelgameel1250]

Amazing lecture Leila your speed of talking is exactly what someone need to absorb new information form a lecturer You amaze me the more I watch your videos

[@LeilaGharani]

I'm glad you find it easy to follow Kamel : )

[@bibekanandabhattacharjee952]

Many thanks for the explanation @Leila Gharani . I bumped into this when I was looking for a video on sumif. I am faced with a situation and it can be achieved by sumif/sumifs. An example of the data in Spreadsheet Date Team Possession 17/Sep/2023 Arsenal 75 17/Sep/2023 Everton 25 21/Sep/2022 Arsenal 59 21/Sep/2022 PSV 41 24/Sep/2022 Arsenal 46 24/Sep/2022 Tottenham Hotspur 54 The first column is Date, the second is team and the 3rd column is Possession. Note that the Date is repeating for 2 rows. The 2nd column is Team and it gets repeated in alternate rows. The third column is Possession. The problem statement: I want to create a formula such that whenever I enter a value for field i.e. the possession, the same field for the same date should be auto-populated by (100-the value entered in the other cell) such that the total of the 'Possession' field is 100%. I think I am able to explain. Could you help me achieve this simple automation ?

[@andrewmoss6449]

1,110 days on from the release of this video, and the fairly recent introduction of dynamic arrays has really got me thinking about how I write formulas! One of the issues I find with functions like SUMIFS and COUNTIFS is that they don't allow for OR conditions or multiple column arrays. Even doing a conditional SUMPRODUCT with ranges that span over two columns will result in an error. This is where I've found *SUM(IF(…* or *COUNT(IF(…* formulas to be really useful because they provide a lot more flexibility. I think before - there was a reluctance to use formulas like this because you had to press Ctrl + SHIFT + RETURN. Now it just feels a lot better.

[@mahindufrancis9998]

Don't know what you talking about but I will experiment it out

[@bprmel]

Nice, short and to the point plus cheerful personality 😊👍👌

[@LeilaGharani]

Glad you like it :)

[@ben-uy1ud]

Thanks Leila for going slow so that the brain can grasp fully, you are the bestest teacher!!

[@unfassbarX]

your videos are a blessing

[@LeilaGharani]

I'm glad to hear that Alexandra :)

[@russell7140]

Thank You! Your videos are very helpful

[@prasannagurung8915]

Great Learning Madam . Looking forward for more useful videos. Regards

[@violenachary956]

Thank you so much i understand sumifs much better

[@chocobabychibi5160]

Thank you Leia! I couldn't do these in my job excel assessment test. I am now prepared for next time. Your video is the best explaining these functions!

[@sunilmarodkar9337]

@Leila Gharani You have covered subtle details and possible mistakes in very simplified way, it saved my day. Great job and Thanks

[@ayeshatariq9578]

I have a test tommorow, this video has helped me alot

[@LeilaGharani]

Good luck!

[@chrisneeves4721]

Thanks for the tutorial. Just a note for everyone, if you are using merged cells, you need to treat them as single cells in the formulas ranges e.g. if you had A1 merged to C1 for criteria_range1, and you selected 4 rows Excel will treat this as A1:C4, but this will cause the formula to error with #VALUE!, just change the the range to A1:A4 and this will work. This took me a while to figure out why my formula would not work.

[@LeilaGharani]

Thanks for the input Chris! One more reason to avoid merged cells :)

[@HamayonKhan-g3m]

WOW! What an explanation I really understood the entire logic behind the story and thanks for uploading such lovely videos hope to watch more of such a great lectures.

[@adilsayeedofficial]

Leila, you explained this so wonderfully. Like, the small details mattered such as when to click the criteria and where to FIND THAT criteria. It just made it so easy to understand. Please keep it up! Thank you!!

[@LeilaGharani]

I'm very glad to hear that Adil! Thanks for your feedback and your support.

[@kkind46]

indeed, the way you explanation is one of the best source i have ever noticed. You are really great!

[@LeilaGharani]

I'm happy to hear that Mohammad.

[@kkind46]

@@LeilaGharani ❣❣❣❣👍👍👍

[@raymondgwati8559]

Hie Leila Gharani, your videos are very helpful. Full of content you're merely a humble tutor

[@rabihel-habta313]

OMG OMG, I CAN'T EXPRESS HOW MUCH YOUR VIDEOS ARE SAVING MY LIFE!!!, EVEN AN OLD VIDEOS, THANK YOU VERY MUCH :)

[@LeilaGharani]

Happy to hear that!

[@adarshan1995]

Tutoring is excellent. Please keep uploading more video of this kind for various excel functions.

[@LeilaGharani]

Will do. Glad you like the tutorials.

[@brendam7636]

thank you so much Leila, you helped me figure out how to sumifs using multiple criteria, and not having to use range/table complexity. THANK YOU so much!

[@abirchatterjee8131]

One of the best excel videos I have seen so far, wow moment for me ma'am. Please do a series on all financial formulas.

[@LeilaGharani]

Thanks for the kind feedback. Really glad you like it.

[@abirchatterjee8131]

Please do a series on all financial formulas too

[@jam-hu]

Thank you Leila. You make some of the best Excel videos out there. And I could listen to your voice all day ;)

[@LeilaGharani]

I'm glad to hear that :)

[@charlotteramos2267]

You are the best!!! You're videos never pass out!! Just learn how to condition the formula to a cell...

[@VigneshS-gv4eq]

Thanks a ton! I was looking for Countifs formula with 3 criteria since last 2 days and finally I got it here :)

[@LeilaGharani]

Glad it helped!

[@pacoru1350]

¡Gracias!

[@LeilaGharani]

Thank you!

[@martinry5412]

HEBAT HEBAT!!! Pinter banget. Thank you Leila! Your videos are very helpful. Amazed.

[@foodycollada5307]

u r my new teacher

[@pumpgeek]

Great job - well presented

[@robbiepool2052]

Thanks for your video. I learn a lot. I recently started using the if function and enjoying it. Let me give you a scenario: A is Low Risk 0 - 19%, B is Lower Risk 20% - 39%, C is Medium Risk 40% - 59%, D is High Risk 60% - 79%, E is very High Risk 80% - 100%. For each one, I do an IF function for True and False. The very formula gives me a "True". I use =. Here is my formula: =IF(AA6

[@peterpackiaraj]

you can go for conditional formatting

[@ttime-lu9ko]

Excellent tutorial, I now know how to perform SUMIFS, AVERAGEIFS and COUNTIFS. You helped me to understand the steps to take. Thank You!

[@LeilaGharani]

I'm glad to hear that Wynne. You're very welcome :)

[@omarcamara9722]

Thank you Leila, you are truly a tutor. You explained so clearly I'm impressed.

[@LeilaGharani]

Glad you think so!

[@Bsmart8]

Laila you are a great teacher.... lots of hearts for u

[@tha2irtalib343]

You are great , that was simple , helpful , and clear , thanks alot .

[@LeilaGharani]

You're very welcome. Glad you find the video helpful.

[@khamarulaqmalbinanuar5586]

OMG TQSM!! IVE BEEN SEARCHING MANY VIDEOS TO UNDERSTAND THIS FX FOR WEEKS IN ORDER TO SUBMIT MY PROJECT TO MY LECTURER 😭

[@jminta9966]

Amazing video 👌🏿

[@vernonaspinall1629]

I was fortunate to find this video on the use of wildcard searches, and used that with your video about the use of COUNTIFS. Thank you!

[@seaonau]

Hi I have watched a few of your video tutorials and think that you are a great teacher. Thank you :)

[@LeilaGharani]

Thank you very much. I'm glad you like the tutorials. Thank you for your support :)

[@jerrydellasala7643]

I’ve used the SUMIFS function before but just got stuck this morning. This refresher unblocked my brain! Thank you!!

[@Dhrubajyotiabc]

Love this video, wonderful information for working professionals, lots of from India for you.

[@LeilaGharani]

Glad to hear that :)

[@michebernier5298]

countif just saved me hours -- Thank you so much ! The instruction is easy to understand, and examples can be applied easily to any situation. Again, thank you so much.

[@peteradeteacher1380]

true. check this ones too. and dont forget tolike n subscribe kzbin.info/door/VZha-RnaYA9ALGgirEYXIg please dont forget to like n subscribe

[@giftnew2998]

Also try dcount farmula better than countifs kzbin.info/www/bejne/i6iYfpx7eqaSaaM

[@michebernier5298]

@@peteradeteacher1380 the video in your link was not well done and almost impossible to understand through the accent, and it's rather tacky to add links for your own page, when Leila has worked very hard to establish herself, and many of us watch her video's regularly

[@Viper4ever05]

You saved my life thank you

[@nareinnaveen8380]

Awesome work.

[@hdehghani2278]

After watching many videos I finally understood how to use SUMIFS and the logic behind it.Thank you x

[@LeilaGharani]

Glad to help!

[@sarabjitsingh6158]

thnku so much mam for excellent lecture on sumifs formula.i'm sarabjit singh from india.

[@LeilaGharani]

It's my pleasure :)

[@vishaltad4242]

Thanks Leila Gharani……. I saw your several videos which connects with excel functions and It has been very helpful to me. ...! .

[@LeilaGharani]

You're very welcome. I'm glad the videos are helpful!

[@Shafickhader]

Very useful lecture, thanks.

[@richsen]

Love your videos - you have no idea how much your videos help me with my work!!

[@peteradeteacher1380]

SHE IS GREAT AND INSPIRED ME TO DO SOME TOO. check out this too kzbin.info/www/bejne/Z5LFm3WoiNaHpM0 kzbin.info/www/bejne/ommTnn-llrSSY9E kzbin.info/www/bejne/i36xgJ6odsh4bqM kzbin.info/www/bejne/iYLFg5SqqtVlp7c dont forget to like n subscribe.

[@smsaif0074]

Really your teaching method is excellent..proud of you Leila

[@jalwa55]

Honestly speaking it is the most simplest explanation ever. Thank You. Great video.

[@LeilaGharani]

You're welcome. Glad you like it.

[@3dstockroom]

YOU ARE BEST TEACHER

[@davoodbarzegari4080]

دست شما درد نکند. thank you

[@rosythapa2686]

I think I will never forget what I learnt from this video. It is so helpful for the beginners. Thanks alot for explaing so well.

[@tofiqbayramov1042]

Such a great video, thanks for sharing.

[@LeilaGharani]

Our pleasure!

[@jasminej9216]

So helpful ! Thank you!

[@Lerxsty2112]

The part of this that helped me was using quotations around the comparison operators. Thanks!