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.
