Before explaining the Google Sheet function SUMIFS, let me tell you one thing. If you know how to use SUMIFS in offline Spreadsheet application MS Excel or Office 365, you can skip this tutorial as both are same. Else continue reading.
Our approach of explaining Google Sheet functions is different compared to others. We follow simple easy to learn approach with enough screenshots. That’s the reason we get tremendous response to our Google Sheet Tips and Tricks eBook.
I was extensively using SUMIF, not SUMIFS, for my datasheet evaluation purposes. Because it was enough for my purposes and I was sometimes using DSUM a similar function to SUMIFS.
SUMIF and SUMIFS are two independent functions in Google Sheet. Then? What is the difference of SUMIFS and SUMIF? The former allows multiple conditions for evaluations but the latter only one condition. This Google Spread Sheet tutorial is solely for the use of SUMIFS in Google Sheets as SUMIF we already explained earlier that you can find by following the below link.
Here also we will follow our regular Google Sheet tutorial method for dealing with functions. First we will explain the syntax then to the simple example with pictures. Here we go!
Create a sample database as below.
The purpose of SUMIFS is; it sum values in a range based on multiple matching criteria.
Here in our example we will pick one person from the above data and one area he involved to find his total Sales Value in that area. Here the function will check two conditions. First match the name of the person and then the area. That means two ranges and two criteria.
=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)
In the above sample data “sum-range” is the field “Sales Value”, “range1” is the field “Name of Sales Person” and “range2” is the field “Area”. Criteria we will explain later.
Now once again explaining the arguments;
sum_range – It’s the range to be summed. This field contains value.
range1 – The first range to evaluate.
criteria1 – The criteria to use on range1.
range2 – [optional, but if you have no range 2, better use SUMIF not SUMIFS] The second range to evaluate.
criteria2 – [optional] The criteria to use on range2.
If you find the above syntax and arguments tough to follow, see the screenshot below. The SUMIFS function is explained.
Everything is explained about SUMIFS in the above screenshot example. Carefully check it and prepare same data and apply the function as above to learn. Any doubt, write us in comment section below.