These SUM Tricks Will Blow Your Mind (No, Seriously)

00:12:10
https://www.youtube.com/watch?v=K33AQnL_cYA

Summary

TLDRThe video demonstrates how to leverage the SUM function in Excel to perform tasks typically associated with COUNTIFS, SUMIFS, and AVERAGEIFS. It explains how to count and sum sales data based on multiple criteria, using logical tests and avoiding double counting. The video also covers conditional averages and searching for specific text within data, highlighting the flexibility of the SUM function in various scenarios. Techniques such as using the SIGN function and the double unary are discussed to enhance formula accuracy and efficiency.

Takeaways

  • ➕ SUM can replace COUNTIFS and SUMIFS.
  • 🔍 Use logical tests within SUM for counting.
  • ⚖️ Avoid double counting with the SIGN function.
  • 📊 Calculate conditional averages using SUM.
  • 🔢 Convert TRUE/FALSE to numeric with double unary.
  • 📝 SUM can search for text in data.
  • ➕ Add conditions for OR criteria in SUM.
  • 📈 Use filters to verify SUM results.
  • 📉 Handle multiple criteria without switching functions.
  • 🎓 Explore advanced Excel formulas for more techniques.

Timeline

  • 00:00:00 - 00:05:00

    The video introduces the SUM function in Excel, explaining its versatility in performing tasks typically handled by COUNTIFS, SUMIFS, and AVERAGEIFS without needing to switch functions. Using a dataset of daily sales, the presenter demonstrates how to count sales of mice exceeding $1,000 using both COUNTIFS and SUM, highlighting how SUM can handle multiple conditions by treating them as logical tests and converting them into numeric equivalents for counting. The video emphasizes the importance of using the SUM function to avoid limitations of COUNTIFS, especially when dealing with 'OR' conditions and potential double counting.

  • 00:05:00 - 00:12:10

    The presenter continues by explaining how to sum sales values based on multiple criteria, including the use of the SIGN function to prevent double counting when conditions apply to different columns. The video also covers how to calculate conditional averages using SUM, showcasing the process of summing sales values and dividing by the count of relevant rows. Additionally, the presenter introduces techniques for searching text within data, demonstrating how to count occurrences of specific words in product reviews using SUM, SEARCH, and ISNUMBER functions, ultimately illustrating the flexibility and power of the SUM function in various scenarios.

Mind Map

Video Q&A

  • Can the SUM function replace COUNTIFS?

    Yes, the SUM function can perform the same tasks as COUNTIFS by using logical tests.

  • How do you avoid double counting in SUM?

    Wrap conditions in the SIGN function to prevent double counting.

  • What is the purpose of using the double unary (--) in formulas?

    The double unary converts TRUE/FALSE values into numeric equivalents (1 and 0).

  • Can SUM handle multiple criteria?

    Yes, SUM can handle multiple criteria by using addition or multiplication of conditions.

  • How do you calculate conditional averages with SUM?

    You can calculate conditional averages by summing values and dividing by the count of rows that meet the criteria.

View more video summaries

Get instant access to free YouTube video summaries powered by AI!
Subtitles
en
Auto Scroll:
  • 00:00:00
    Sum, the first function we all learned.
  • 00:00:03
    It's great for adding up columns and
  • 00:00:05
    rows. But what if I told you sum can
  • 00:00:07
    actually do the job of count ifs, sum
  • 00:00:09
    ifs, even average ifs without switching
  • 00:00:11
    functions. In fact, it can also overcome
  • 00:00:14
    the limitations of those functions.
  • 00:00:16
    Surprised? There's more. So, let's get
  • 00:00:18
    started. We'll use this data set for the
  • 00:00:21
    examples. It's daily sales by category,
  • 00:00:24
    item, region, units sold, unit price,
  • 00:00:27
    and sales value. It's formatted in an
  • 00:00:30
    Excel table and you can see here the
  • 00:00:32
    name of the table is sales. So I'll be
  • 00:00:34
    referring to the data using the table
  • 00:00:35
    name and column names which will make
  • 00:00:37
    the formulas quicker to write. Okay,
  • 00:00:39
    let's say you need to count how many
  • 00:00:41
    days you sold more than $1,000 worth of
  • 00:00:43
    mice. The obvious way is to use the
  • 00:00:45
    count ifs function. And the first
  • 00:00:48
    criteria range are the sales values. The
  • 00:00:51
    criteria is where they are greater than
  • 00:00:54
    1,000. By the way, strictly speaking,
  • 00:00:57
    criterion is singular and criteria is
  • 00:00:59
    plural. But in Excel and everyday usage,
  • 00:01:02
    it's widely accepted to refer to a
  • 00:01:04
    single condition in a formula as
  • 00:01:06
    criteria. So that's what I'll be using.
  • 00:01:09
    All right. The second criteria range are
  • 00:01:11
    the items and the criteria is mouse.
  • 00:01:15
    Close parenthesis on count ifs and we
  • 00:01:18
    get three. that is the formula counts
  • 00:01:20
    records where the sales value is greater
  • 00:01:23
    than 1,000 and the sales item is mouse.
  • 00:01:27
    Now a few people don't know that the sum
  • 00:01:29
    function can also do this. I'll write
  • 00:01:31
    the formula and then I'll explain how it
  • 00:01:33
    works. Each set of conditions goes
  • 00:01:36
    inside parenthesis. Again I want the
  • 00:01:39
    sales value where it's greater than
  • 00:01:41
    1,000. Close parenthesis on that
  • 00:01:43
    condition. Then I multiply that by the
  • 00:01:46
    next condition which is where the item
  • 00:01:49
    equals mouse close double quotes close
  • 00:01:52
    parenthesis on my second condition. So
  • 00:01:54
    what we have here are two logical tests
  • 00:01:56
    one for each condition close parenthesis
  • 00:01:58
    on sum and we get the same result three.
  • 00:02:02
    So how does it work? Well if I select
  • 00:02:05
    the first logical test you can see it
  • 00:02:07
    returns an array of true and false
  • 00:02:09
    values one for each cell in the sales
  • 00:02:11
    value column. true is returned where the
  • 00:02:13
    sales values are greater than 1,00. And
  • 00:02:16
    then the second logical test does the
  • 00:02:18
    same for the item column, returning true
  • 00:02:21
    where the cell contains mouse. Now, the
  • 00:02:23
    sum function can't add up these true and
  • 00:02:25
    false values, but they have a numeric
  • 00:02:27
    equivalent of one for true and zero for
  • 00:02:29
    false. And we can coersse them into
  • 00:02:31
    their numeric equivalents by performing
  • 00:02:33
    a math operation on them. And that's
  • 00:02:36
    where multiply comes in. To illustrate
  • 00:02:38
    what's happening under the hood, I've
  • 00:02:40
    placed the two logical tests in separate
  • 00:02:42
    columns beside the data. Here you can
  • 00:02:45
    see the true and false results. And when
  • 00:02:47
    they're multiplied by one another, we
  • 00:02:49
    get zeros and ones because false * false
  • 00:02:52
    is the same as 0 * 0, which equals 0.
  • 00:02:56
    True * true is the same as 1 * 1, which
  • 00:02:59
    equals 1. And true * false is the same
  • 00:03:02
    as 1 * 0, which equals 0. and then the
  • 00:03:05
    sum formula simply adds them up. So it's
  • 00:03:09
    only where both conditions are true that
  • 00:03:11
    the record is included in the count. In
  • 00:03:14
    other words, the conditions are treated
  • 00:03:16
    as and criteria. That is in this example
  • 00:03:19
    where the sales value is greater than
  • 00:03:21
    1,00 and where the item is mouse. What
  • 00:03:25
    if we wanted to count the rows where
  • 00:03:27
    there are sales for mice or keyboards?
  • 00:03:29
    If we try that with count ifs. So the
  • 00:03:32
    item is keyboard or the item is mouse.
  • 00:03:38
    Close parenthesis and we get zero
  • 00:03:40
    because count ifs can't handle all
  • 00:03:43
    criteria but some can. Again for sum we
  • 00:03:46
    wrap each condition in parenthesis. The
  • 00:03:49
    first one is where the item equals mouse
  • 00:03:52
    close parenthesis. And instead of
  • 00:03:54
    multiplying the conditions we add them
  • 00:03:56
    together. The next condition is where
  • 00:03:58
    the item equals keyboard close
  • 00:04:01
    parenthesis on my second condition.
  • 00:04:03
    Close sum and we get eight. We can see
  • 00:04:06
    the conditions here and we add them
  • 00:04:09
    together to coersse the boolean values
  • 00:04:11
    into their numeric equivalents of 1 and
  • 00:04:13
    zero. By adding the conditions, we pick
  • 00:04:16
    up the rows where any one of the
  • 00:04:18
    conditions are true. That is where the
  • 00:04:20
    item is mouse or keyboard. And that's
  • 00:04:23
    fine where the conditions apply to the
  • 00:04:25
    same column as we have in this formula
  • 00:04:28
    referring to the item column. But if the
  • 00:04:31
    conditions apply to different columns,
  • 00:04:32
    there's a risk of double counting. For
  • 00:04:35
    example, let's say we want to count the
  • 00:04:37
    rows where the item equals mouse plus
  • 00:04:40
    for
  • 00:04:41
    or the sales value is greater than
  • 00:04:45
    1,000. Close parenthesis on my second
  • 00:04:47
    condition. Close sum and we get 26.
  • 00:04:51
    Let's look at what's happening under the
  • 00:04:53
    hood. You can see on row 24, both
  • 00:04:56
    conditions were met and true plus true
  • 00:04:59
    equals 2. This means we're double
  • 00:05:02
    counting this row. To avoid double
  • 00:05:04
    counting, we can wrap the conditions in
  • 00:05:06
    the sign function. So we just pop that
  • 00:05:09
    in the front. Add a parenthesis around
  • 00:05:12
    the two conditions. And what sign does
  • 00:05:14
    is converts any positive number to one,
  • 00:05:18
    any negative number to minus one, and
  • 00:05:20
    zero remains at zero. So if we evaluate
  • 00:05:23
    sign, you can see we're left with ones
  • 00:05:26
    and zeros. So there's no risk of double
  • 00:05:29
    counting. And now we get the correct
  • 00:05:31
    count of 23. So remember always wrap all
  • 00:05:34
    conditions in the sign function to avoid
  • 00:05:37
    double counting. That wraps up
  • 00:05:39
    conditional counting which overcomes the
  • 00:05:41
    limitation of countifs treating the
  • 00:05:42
    conditions as and criteria. Similarly,
  • 00:05:45
    we can do the same for sum ifs which
  • 00:05:47
    also treats all conditions as and
  • 00:05:49
    criteria. For example, here we can see
  • 00:05:52
    this sum ifs is summing the sales values
  • 00:05:54
    where the category is toys and the
  • 00:05:56
    region is east. And we can do the same
  • 00:05:59
    with the sum function, multiplying the
  • 00:06:01
    sales values by the two conditions. But
  • 00:06:04
    what if we want to sum the sales values
  • 00:06:06
    for the east region where the category
  • 00:06:07
    is toys or clothing? Let's take a look.
  • 00:06:11
    We want to sum the sales values and then
  • 00:06:14
    we multiply that by our conditions. The
  • 00:06:17
    first condition is where the region
  • 00:06:18
    equals east. Close parenthesis on that
  • 00:06:21
    condition times. Now, our next two
  • 00:06:24
    conditions are all criteria. And because
  • 00:06:26
    they both apply to the category column,
  • 00:06:28
    there's no risk of double counting. So,
  • 00:06:30
    I don't need the sign function here. But
  • 00:06:32
    I do need to wrap both conditions in
  • 00:06:34
    another set of parenthesis. And the
  • 00:06:36
    first condition is where the category
  • 00:06:38
    equals toys plus the next condition is
  • 00:06:42
    where the category equals clothing.
  • 00:06:45
    Close parenthesis on my second or
  • 00:06:47
    condition. Close it on both conditions
  • 00:06:50
    and close sum. and we get
  • 00:06:53
    775117. Let's use the filters to check
  • 00:06:55
    the results. So we want clothing and
  • 00:06:58
    toys and the region is east. Let's
  • 00:07:02
    select the values and you can see in the
  • 00:07:04
    status bar the sum is
  • 00:07:07
    7751.17. So we can see it's calculating
  • 00:07:09
    as expected. Next let's look at
  • 00:07:12
    conditional averages because these are a
  • 00:07:14
    bit different. Here I'm averaging the
  • 00:07:16
    sales values for t-shirts and it's super
  • 00:07:18
    easy with average ifs. To be clear, it's
  • 00:07:21
    taking the sales values, not the average
  • 00:07:24
    unit price for t-shirts. With sum, it's
  • 00:07:27
    not as straightforward because first we
  • 00:07:30
    have to sum the sales values for
  • 00:07:31
    t-shirts. That's the first part of sum.
  • 00:07:34
    Then to get the average, we need to
  • 00:07:36
    divide by the count of rows containing
  • 00:07:39
    t-shirts. And because this returns an
  • 00:07:42
    array of boolean true and false values,
  • 00:07:44
    we need to coersse them into their
  • 00:07:46
    numeric equivalents of ones and zeros,
  • 00:07:49
    which we do with the double uny, which
  • 00:07:51
    is simply two minus signs. When they're
  • 00:07:53
    evaluated together, we get our array of
  • 00:07:56
    ones and zeros, which sum can then add
  • 00:07:59
    up. And together, we get the same result
  • 00:08:01
    as average ifs. Of course, you wouldn't
  • 00:08:03
    use sum here, but if you want all
  • 00:08:06
    criteria, for example, the average sales
  • 00:08:08
    for two items like t-shirts and jackets,
  • 00:08:12
    then sum is your friend. Let's take a
  • 00:08:14
    look. So, we're finding the average of
  • 00:08:17
    the sales values multiply by the
  • 00:08:19
    criteria. Open parenthesis, and we need
  • 00:08:22
    to wrap both criteria in another set of
  • 00:08:24
    parenthesis. So my first condition is
  • 00:08:27
    going to be where the item equals
  • 00:08:30
    t-shirts and then plus cuz this is all
  • 00:08:32
    criteria where the item equals jacket
  • 00:08:36
    close parenthesis on the second
  • 00:08:38
    condition close parenthesis around both
  • 00:08:41
    conditions close sum and to make it
  • 00:08:44
    quick I'm just going to copy the
  • 00:08:46
    criteria and then to find the average we
  • 00:08:49
    need to divide it by the sum of the
  • 00:08:52
    count of my two items close parenthes.
  • 00:08:55
    es on my
  • 00:08:56
    denominator. Press enter and we get
  • 00:09:00
    1,387 and 19. Remember here we didn't
  • 00:09:04
    need to use the sign function because
  • 00:09:06
    there's no way we could double count the
  • 00:09:08
    criteria because they're operating over
  • 00:09:10
    the same
  • 00:09:11
    column. Of course, these techniques can
  • 00:09:13
    be used to find the minimum and maximum
  • 00:09:15
    values based on all criteria. So, you
  • 00:09:17
    can try that for homework. If this has
  • 00:09:20
    already clicked and you're thinking, I
  • 00:09:22
    want more of this, you'll love my
  • 00:09:23
    advanced Excel formulas course. It's
  • 00:09:25
    packed with examples like this for Excel
  • 00:09:27
    functions, but it goes way beyond.
  • 00:09:29
    You'll learn how to combine functions,
  • 00:09:32
    troubleshoot complex logic, and write
  • 00:09:34
    formulas that adapt as your data
  • 00:09:36
    changes. You can check it out in the
  • 00:09:38
    description or pin comment. Another use
  • 00:09:40
    for these techniques is to search inside
  • 00:09:42
    text for matching words. For example,
  • 00:09:45
    here I have some product review data.
  • 00:09:47
    And let's say I want to count how many
  • 00:09:49
    reviews mentioned fast. Starting with
  • 00:09:52
    sum and then search, I can locate the
  • 00:09:56
    starting position of the text fast in
  • 00:09:58
    the review column. So the text I'm
  • 00:10:00
    looking for is fast. Where am I looking?
  • 00:10:03
    In the review column close parenthesis.
  • 00:10:06
    And then if we evaluate search, you can
  • 00:10:08
    see it returns the starting position of
  • 00:10:11
    fast in the text string. And if it's not
  • 00:10:13
    found, it returns an error. Now, sum
  • 00:10:15
    can't add up errors. So, let's use is
  • 00:10:18
    number to check if any numbers are
  • 00:10:21
    found. And then if we evaluate that, you
  • 00:10:24
    can see it converts those errors into
  • 00:10:26
    false. And where numbers were found, it
  • 00:10:29
    converts them into true. Now, remember,
  • 00:10:31
    sum can't add up true and false values.
  • 00:10:33
    But with the double uny, which is the
  • 00:10:34
    two minus signs, we can coersse them
  • 00:10:37
    into their numeric equivalent of ones
  • 00:10:39
    and zeros. All I need to do now is close
  • 00:10:42
    parenthesis and sum adds them up. So we
  • 00:10:45
    can see there are eight reviews where
  • 00:10:47
    the word fast was used. But what if we
  • 00:10:50
    want two criteria like reviews that
  • 00:10:53
    contain fast or affordable? Again, we'll
  • 00:10:56
    start with sum and then because we have
  • 00:10:59
    multiple criteria and both could be
  • 00:11:00
    true, we need to wrap them in the sign
  • 00:11:02
    function to avoid double counting. Then
  • 00:11:04
    I'll use is number with search to look
  • 00:11:08
    for the first one is fast. Where are we
  • 00:11:11
    looking in the review column? Close
  • 00:11:13
    parentheses on search. Close is number
  • 00:11:17
    or is number. Search. The next word is
  • 00:11:21
    affordable. Where are we looking? Again
  • 00:11:24
    in the review column close parenthesis
  • 00:11:27
    on search. Close is number. Close sign.
  • 00:11:30
    Close sum. Press enter. And we get 13.
  • 00:11:33
    Let's check. I've set up some
  • 00:11:34
    conditional formatting that highlights
  • 00:11:36
    the cells that contains either one of
  • 00:11:38
    those words. Let's filter the
  • 00:11:40
    data and I'll select the cells. You can
  • 00:11:43
    see the count in the status bar matches
  • 00:11:45
    my formula result. Perfect. Sum gives
  • 00:11:48
    you control over what your formulas
  • 00:11:50
    calculate. But what if you need them to
  • 00:11:52
    behave differently across specific rows?
  • 00:11:55
    If you've ever wanted your formulas to
  • 00:11:56
    do one thing in some rows and something
  • 00:11:58
    completely different in others, there's
  • 00:12:00
    a function for that. And most people
  • 00:12:02
    have no idea how useful it actually is.
  • 00:12:05
    I show you how it works in this video.
  • 00:12:07
    Click here to see it in action.
Tags
  • Excel
  • SUM function
  • COUNTIFS
  • SUMIFS
  • AVERAGEIFS
  • conditional counting
  • double counting
  • logical tests
  • text search
  • advanced formulas