Let's take a look at how we can sum or average values by criteria.
So right here we have the actual sales by different apps with their region and their division listed
here.
Our data set goes all the way down to line 167.
Now based on this I want to solve for the following.
My first task is to get the total sales for the app specified in this cell right here.
Similar to what we saw with CountIf
by conditions, we also have two different choices here. We can use the "SumIf" function or the "SumIfs" function.
The "SumIfs" function came since Excel 2007 and it can account for more than one
criteria.
But if you have one criteria you can use either of these formulas but there is one thing you have to
be aware of. The arguments required for these two formulas are different.
Let me show you by solving this first with this "SumIf" function.
So just type in SumIf, open the bracket, the first argument is the range but it's not our sum range
because the sum range is right here.
It looks like this is the range where the criteria is sitting on. My criteria is "Perino".
"Perino" is an app.
So it's sitting somewhere here.
This is the first argument from my SumIf function.
The range of my criteria. Highlight this, control+shift+down, control+backspace to jump back up. Next argument
is the criteria itself.
In this case it's sitting in this cell.
And the last argument is my sum range which is now my actual sales. So control+shift+down, control+backspace
to jump back up, close the bracket, press enter.
That's the total sales for Perino.
Now let's also solve this with the SumIfs version.
Here's the difference.
When we opened the bracket we see the sum range first and then we see the different criteria ranges.
Microsoft had to switch over the arguments here because they don't know how many criteria ranges you're
going to have but you're always going to have sum range.
So they had to bring the sum range forward in the argument list which for us is our actual sales.
Next argument is the range where our criteria is sitting on.
So this is the app because our criteria is an app.
Its range is right here.
Last Argument is the criteria itself which is Perino here.
Close bracket.
press enter, okay.
So don't forget that the order of arguments is different between the SumIf and the SumIfs versions.
Now the great thing about this is it's dynamic.
If I change this to Arcade, I get the sum of Arcade. Change these to Pes, I have the sum for Pes.
Next task, let's get the total sales for the division and region specified below.
So right here we want to select or type in the division. We want to type in the region and we want to
get the total sales.
So we have two conditions which means we need to use which version? The SumIfs version because that allows
us to have multiple conditions.
Sum range, actual sales, criteria range 1. That's either going to be our division column or the region
column.
The order doesn't matter at all.
So I'm just going to start off with the division column.
Next is the criteria itself which is sitting right here.
I have one more condition and that's the region
but first I need the range where this region is sitting on which is right here.
And then the region itself which is specified here, close bracket, press enter.
That's the total sales for division Game in region Europe.
Now our next task is to get the average sales for the division and region specified below and exclude
zero values.
So we can see here we have a bunch of zero values here
and because we want to get the average they're going to influence an average.
So let's say these are actually supposed to be empty and shouldn't be included in our final average
value.
So this time, instead of using the SumIfs function, which function do I need to use?
The AverageIfs function.
Now with the AverageIfs we also have two versions of it we have the AverageIf function as well as the
IFS versions. And the order of arguments is also different for these. With the IFS version
the average range comes in first.
So that's the range we want to get the average of which is our actual sales.
So similar to the SumIfs version. Our criteria range one is division here, the division column.
The criteria itself is our division. The second criteria range is our region and the criteria itself is sitting
right here.
If I just close the bracket and press enter I'm going to be including the zero values in this average
calculation.
So basically I have the exact same formula I had here
Except that I've replaced the SumIfs with the AverageIfs but now I also want to exclude the
zero values. I can add another argument to this at the end. My criteria range is the column where I have
the numbers in and the actual criteria is it should be greater than zero. And because this is coming
right after the formula argument separator.
I have to put it in quotation marks.
Then I need the "&" and zero.
Now you can put the zero value also inside the quotation mark and Excel is going to know to translate
this to a number.
So now when I a press enter I get a bigger value because my 0 values here are excluded.
So that's how you can use the SumIfs and the AverageIfs functions to get these sum or the average
of a range of numbers based on criteria.