The SUMIFS function
is what you'll want to use
to add only values that meet specific requirements.
Very much like the SUMIF function,
except you can include multiple criteria
for your IF statements.
Let's take a look.
Let's say we want to sum the total sales amount
for employee Emerson.
So start by typing equals SUMIFS.
And the first argument is the sum_range.
Now this will be the range of values that will be added.
In this case, we need to select the data
in the Sales column.
Then add a comma.
The next two fields
are criteria_range1 and criteria1.
So criteria_range and criteria work as a pair.
So the first is the data range
where Excel will look for the criteria.
And the second is where you'll state what that criteria is.
And you can create up to 127 of these criteria pairs.
So in this example,
because we want to sum the amount for a specific employee,
our first criteria_range will be the Employee column.
Then add a comma
and the first criteria
will be the name of the employee
that we want to add up the values for.
In this case, that's "Emerson" in quotation marks
because it's text.
And press Enter,
and our total is $19,365.
Which we can confirm if we filter our data by Emerson.
Let's add a second criteria.
So come to the end of your formula and add a comma.
So let's say we want to sum the amount
Emerson did for shirts.
This means that for our second criteria range,
we'll need to select the cells in the Product column.
Add a comma, and for our second criteria,
we'll type "shirts" in quotation marks,
press Enter,
and our result
is $11,142.
Let's say we instead need to pull Emerson's expenses
for the shirts.
To do this, we need a new SUMIFS function
because the expenses will be a new range of values to sum.
And while you can have multiple criteria,
you can only have a single range of values to sum.
So type equals SUMIFS,
and, in the expenses tab,
we'll select the data in the Expenses column
for the sum_range.
Add a comma
and criteria_range1
will again be the employee names
because we need to filter for Emerson again.
So criteria1
will be "Emerson" in quotation marks,
but then our criteria_range2
will be the Product column from the sales tab.
And for criteria2
we'll again do "shirts" in quotation marks
and the expenses for his shirts
are $1,171.
And he only sold shirts in February and April.
So if we filter Emerson
in the expenses for February and April,
we see that it is
$1,171.
So important to note here,
the values being summed and your criteria
do not need to be adjacent to one another,
or even in the same sheet.
They do, however, have to be the same length of cells.
And it's critical that the data be listed
in the exact same order
so that it references the correct result.
Take a look here,
if any of the selected ranges
that we're using in our formulas
are not the same length as the others, you'll get an error.
One way you can help avoid this
is by converting your data to a Table, if it's not already.
Once it's in a Table format,
Excel automatically expands
any ranges referenced in that Table,
but only for that Table.
Let's delete the subtotal.
And notice if I add a new row here,
and plug in an additional sales amount for Emerson shirts
this amount updates.
The expenses formula, however, displays an error,
because the selected data ranges
are now of different lengths.
To fix this, we need to come to the expenses tab.
And let's make that into a Table as well
and then add a row here
so that all the data ranges
referenced in the formula for the expenses
contain the same number of rows.
And because the expenses formula
references data from separate sheets,
I'm also going to label this row for Emerson May 2020,
even though I don't have an expense amount.
I'm doing this so that it matches the corresponding cell
in the sales table.
Keeping the data in the same order is important
to avoid errors when referencing
across separate data ranges.
For more tutorials like this, click on these links. Thanks!