Excel SUMIFS (better version of SUMIF), COUNTIFS & AVERAGEIFS (Multiple Criteria)



Quickly learn how SUMIFS & COUNTIFS formulas work – and why they are better than SUMIF & COUNTIF formulas.

Download the workbook here:

Get the full course:

The inclusion of the “IFS” formulas since Excel 2007 was a major gain for Excel users. Why? Because they allow you to easily add exceptions to your sum, average and count calculations. Don’t make your life more difficult by trying to use excel filters to get your sum. Use the SUMIFS formula instead. You can SUM based on multiple criteria. You can also use SUMIFS to sum if numbers are greater than a certain value.

In this video I will show you a SUMIFS example and explanation. AVERAGEIFS works in the same way.

COUNTIFS function is shorter than SUMIFS and AVERAGEIFS because it doesn’t need the additional sum range. It only needs the criteria range. It counts the number of times the criteria is met.

Not sure if you should use SUMIF or SUMIFS? Stick to SUMIFS. This way you’re on the safe side. In case you ever need to add more criteria, you can just expand on the arguments inside the formula. If you used SUMIF, you actually have to replace it with SUMIFS.

★ My Online Excel Courses ►

✉ Subscribe & get my TOP 10 Excel formulas e-book for free

EXCEL RESOURCES I Recommend:

GEAR
Screen recorder:
Main Camera:
Backup Camera:
Main Lens:
Zoom Lens:
Audio Recorder:
Microphone:
Lights:

More resources on my Amazon page:

Let’s connect on social:
Instagram:
Twitter:
LinkedIn:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Nguồn: https://federicobellucci.net/

Xem thêm bài viết khác: https://federicobellucci.net/cong-nghe/


Article Categories:
Công Nghệ

Comments

  • Thank you, this is so amazingly useful and you make it so easy to understand ! thank you for the knowledge

    GSW 510 July 19, 2020 3:14 am Reply
  • Pretty good explanation but can you not do everything at light speed please?

    Zak Daowd July 19, 2020 3:14 am Reply
  • Thank you! I am using a knock off, no name brand called “Trio Office” spreadsheet and this worked on it. Huge help. Love and Peace yallzz!

    Greenies Journey GRENZZ July 19, 2020 3:14 am Reply
  • Hi! I like your tutorials alot. I have simple query, can we have more than 1048576 rows in excel? Is there any way to do that?

    Hydrogen Peroxide July 19, 2020 3:14 am Reply
  • sumifs function is more usefull than sumif thankyou very much

    ritesh gangwani July 19, 2020 3:14 am Reply
  • Thanks for spreading knowledge

    ritesh gangwani July 19, 2020 3:14 am Reply
  • 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!

    Vanesa Cova July 19, 2020 3:14 am Reply
  • When you demonstrated sumif, you didn't say how the range and criteria cells (feb in your example) should be formatted. Date? Number? Text? General?

    Andrew Harvey July 19, 2020 3:14 am Reply
  • There are some good tutorials out there, Leila's are the best.

    1stp4ward July 19, 2020 3:14 am Reply
  • Perfect Job, thank you.

    bassey ekpenyong July 19, 2020 3:14 am Reply
  • Very Nice illustrated, very fast and simply understandable … Good Madam… Thank you very much ..

    Jignesh Patel July 19, 2020 3:14 am Reply
  • Nice Video
    #MADExcel

    MAD GAMING & Excel Tricks!!!!!!!!!! July 19, 2020 3:14 am Reply
  • Thank you for excellent easy explanation

    hamad Al-Shammari July 19, 2020 3:14 am Reply
  • Hi Im struggling to find help online. I'm looking for a sum formula that will add the value derived from another cell that has a formula. The formula contains text and numbers so removing the " isn't helping

    naazia ephraim July 19, 2020 3:14 am Reply
  • Thank you so much for putting these videos together! They have been tremendously helpful!! I have been trying to figure out though how to sum multiple criteria that are in the same column and can't figure it out! Using the example you gave in this video, how would you write the formula to figure out the sum of sales revenue for white AND blue shirts sold in February?

    Niki Rousey July 19, 2020 3:14 am Reply
  • 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

    Mildred Souza July 19, 2020 3:14 am Reply
  • Great work dear 🙂

    Mumtaz Mahmood July 19, 2020 3:14 am Reply
  • Outstanding videos, thank you so much they are a big help.

    MIKE FINNIGAN July 19, 2020 3:14 am Reply
  • why doesn't <> work when using multiple criteria with sumifs?

    Stephen Stephen July 19, 2020 3:14 am Reply
  • If I want to calculate between 400 and 500 (>400 <500) ?

    Tolga Köymen July 19, 2020 3:14 am Reply
  • Thank you 🙂

    shpejtim hoxha July 19, 2020 3:14 am Reply
  • I was trying to solve the problem, I failed. After then I got your tutorial, Now my problem is solved. Thanks

    Sayed Tanimun Hasan July 19, 2020 3:14 am Reply
  • Using COUNTIFS, how do I use a word or phrase instead of clicking on a specific cell? For example I want to count instances of the words "civil" and "structural" both from a range of b3:b200. video example of this at 10:33 where i need to substitute "Civil" for "G12" and "Structural" for "H12"? Thank you anyone for help with this.

    jsmpsnn July 19, 2020 3:14 am Reply
  • Thanks. You are great.

    Andres Vasquez July 19, 2020 3:14 am Reply
  • Hi! I am trying to get my head round this: how do I build a formula to calculate the cost of proofreading, if my charges are £95 every 1000 words?

    Carolina Maltese July 19, 2020 3:14 am Reply

Leave a Comment

Your email address will not be published. Required fields are marked *