fbpx
Search
Close this search box.

CP007: aweSUM() – Overview of SUM functions in Excel

Share

Facebook
Twitter
LinkedIn

In the 7th session of Chandoo.org podcast, lets make you aweSUM().

CP007: aweSUM() - Overview of SUM functions in Excel

Before we proceed in to the session, let me remind you:

Subscribe to Chandoo.org Podcast

Do you know that you can subscribe and receive latest episodes of our podcast right to your ears? Use one of the below links to get started.

What is in this session?

Imagine for a second that Excel cannot add up numbers. And no it cant subtract them either. What would that look like?

A glorified Notepad. That’s right. Excel’s ability to add up numbers, along with features like formulas, charts, pivot tables & BHATTEXT() are what make it such a lovely software. May be not the BHATTEXT(), but we all agree that Excel is so versatile and useful because it can add up numbers (and perform other calculations) with ease.

But how well do you know the SUM formulas of Excel?

In this podcast, you will learn,

  • Special personal fruit announcement 😛
  • + operator
  • Status bar & total rows in tables
  • Auto Sum feature
  • SUM() function
  • SUMIFS function
    • When to use it
    • Examples
  • Special cases of SUMIFS function
    • Sum of all positive numbers
    • Sum of all numbers for March 2014
    • Sum of all customers with name = taco bell
  • SUBTOTAL & AGGREGATE functions
  • Other summing functions
    • SUMIF
    • SUMPRODUCT
    • DSUM
  • Conclusions

 

Go ahead and listen to the show

Links & Resources mentioned in this session:

Resources to learn SUM functions

Transcript of this session:

Download this podcast transcript [PDF]

How awesome are your SUM skills?

SUM formulas are corner stone of any dashboard or report or meaningful model. So it no surprise that I use them often. My favorite sum formula is SUMIFS(). It is versatile and powerful. I am learning to love DSUM() too. I will share details about it in an upcoming article.

Meanwhile, tell me what is your favorite SUM formula? What are the tricks and techniques you use when writing SUM formulas? Please share your thoughts using comments.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

6 Responses to “CP007: aweSUM() – Overview of SUM functions in Excel”

  1. Geoff Vautier says:

    I use SUMPRODUCT on almost every spreadsheet I build - it is hugely powerful and lots of uses.

    One of the best is for my financial reporting. I have a row at the top called month and I have a 1 in say the first 3 months (of 12 columns representing the 12 months) and 0 in the other 9.

    Then, to report against the budget for 3 months I simply do a sumproduct on the row of 1's and 0's, and the relevant budget line. is gives we the budget for 3 months. Next month I change the value in column 4 to a 1, and hey presto I have the budget YTD for 4 months.
    A B C D E F G H I J K L M

    Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
    5. Month 1 1 1 0 0 0 0 0 0 0 0 0

    7. Budget Sales 12 11 15 18 19 17 16 23 24 18 18 12

    Sumproduct(B5:M5,B7:M7) = 38

    Change Jul to 1 and the answer becomes 56

  2. Mark Gregory says:

    I have always found SUM using arrays very useful for summing based upon one or more conditions.

    Below is an example using mangos and apples. The data starts in cell A1.

    An array using SUM is created by pressing [ALT][SHIFT][ENTER] after entering the formula.
    As an example enter the formula
    =SUM((A2:A6="Apples")*(C2:C6))
    Pressing [ALT][SHIFT][ENTER] and the formula becomes
    {=SUM((A2:A6="Apples")*(C2:C6))}

    Item Country Amount
    Mangos India $100
    Mangos USA $200
    Apples Mexico $400
    Mangos India $300
    Apples USA $400

    Totals SUM using Arrays
    Apples 800 {=SUM((A2:A6="Apples")*(C2:C6))}
    Mangos 600 {=SUM((A2:A6="Mangos")*(C2:C6))}
    Mangos in India 400 {=SUM((A2:A6="Mangos")*(B2:B6="India")*(C2:C6))}

    Mangos in India with Amount greater than $200
    300 {=SUM((A2:A6="Mangos")*(B2:B6="India")*(C2:C6>200)*(C2:C6))}

    SUM is the only formula so far I have found that works this way.

    • Chandoo says:

      @Mark... Array SUM is really versatile and powerful. I use it many times.

      Btw, for your last example, you can also use SUMIFS.

      This will work just the same.

      =SUMIFS(C2:C6,A2:A6,"Mangoes",B2:B6,"India",C2:C6,">200")

  3. Deepak says:

    @Mark
    Array required [CTRL][SHIFT][ENTER] not [ALT][SHIFT][ENTER]

  4. Gautam says:

    What is the love chart shown in the beginning called. Is there a tutorial on chandoo.org about it?

Leave a Reply