This must have occurred with you multiple times in the office while preparing excel reports when you need to bring particular values from the massive chunk of data based on specific criteria and requirements. Essentially, if you ever want to make a sum with exceptions and specifically, if these exceptions are more than one which is typically the case in actual life. This is when 'sumifs' function in excel comes handy.
Now you
might be already aware of the 'sumif' function
that most people use for their reports and excel sums. However, 'sumifs' is there in the system since Excel
2007 version, and many of us may have just skipped this. It is not only a time-saving
function but also assists you to fetch data from large data arrays using
several criteria in one go. while the 'sumif' function
can fetch data using one exception whereas 'sumifs' can match up to 29 criteria.
Here,
i will demonstrate you this function with the simple example:
Example 1.
Below is the
table we will be working on in this tutorial to demonstrate this function.
As you see in
the above table, we have Year, Product, country, and total
Sales columns. From this data table, we want to get total sales for specific criteria from this table.
Here is the
formula syntax: SUMIFS (sum range, criteria_range1, criteria1,
[criteria_range2, criteria2, criteria_range3, criteria3, … criteria_range_n,
criteria_n] )”
Now we will
use this formula for the above example to fetch expected sales value for year 2019 & product Red trolley bags ex. how much sales amount is there for the Red Trolley bags in 2019.
"Please note,
Sum range= Expected data range for the mentioned criteria
Criteria= Criteria
itself (in our case it is 2019 & Red trolley Bags)
Criteria
Range= Where the criteria data can be found in the table"
In the above example, we have the sum range which is Sales amount selected from D2:D37 ( in Blue color) since we are looking for sales values for particular criteria. Next, we have the first criteria range which year (2019) for us in this example, so we can select year range from A2:A37 (in Red color) and you need to specify which criteria so we have selected F6 number raw which is 2019 for us in this example. Now next is the second criteria range which is a product name for us, so we have selected B2:B37(In red Color) and then criteria itself G6 which is product Red trolley Bags in this case.
Once we are done with the formula, we will get the value as desired (Refer to the below screenshot)
As we were
looking for how much sales were done for Red Trolley bags in 2019 from our
entire table and we got the expected value in one go with this ‘ifsums’
function.
Here is the
image to verify if the resulted value is accurate or not.
And yes, wo
got the correct sum value with this function as highlighted in the above
screenshot.
Now since we
mentioned earlier you can use as much as 29 criteria to fetch data using this ‘sumifs’
function. We will show you one more example to elaborate it little more.
Example
2.
Please see
the below image where we used 3 criteria to get sales amount from the same table
used in earlier example, and here We need to know that how much sale was done for Green trolley Bags in UK in 2020.
And we got
the results here
That’s all. I
hope you like this tutorial and find it easy to follow.
Never stop learning..
Comments
Post a Comment