How to use SUMIFS in Excel

Sum remains one of the most used function in Excel. Many a time there is a need to do summation with multiple criteria’s. In case there are more than one condition requiring check for summation, SUMIFS is the go to function. Of course we can use SUMIF function in places where criteria is only one. In this article we will be looking into step by step process of using SUMIFS function.

John has customer data for his company available. From the available file, he is looking to figure out total spend for Sweden Females and Canada Males. The problem seem complex to him. Lets see how he can use SUMIFS to solve the same. To illustrate, this example explains SUMIFS usage with details.

Below are the key steps to achieve the results:

• Step 1: First step of the input is Range. The column requiring summation will be used in Range. For this case it will be Yearly Spend i.e. Column I. I5:I104 represents Range.

• Step 2: Then fixing the sum range is also important, as this helps in easy replication of formula for next cell entry.

• Step 3: Criteria_Range1 is the Range of cells where we check first criteria. First Criteria is for Gender. Since gender is present in column F. Therefore F5:F104 represents Criteria_Range1.

• Step 4: Similarly, fixing the criteria range is also important as done in Step 2.

• Step 5: Criteria1 is the cell where criteria value of criteria 1 is present. Here the criteria value 1 is present in cell K5. Thus K5 is Criteria1.

• Step 6: Then as a next step add Criteria_Range2. Second Range is Country values. Country values are present in column G. G5:G104 represents Criteria_Range2.

• Step 7: Criteria2 is the cell where comparison value of criteria 2 is available. Here L5 has criteria value 2. Finally, hit Enter to populate the results.

• Step 8: Finally copy the formula from cell M5 to M6. This generates the required values for Canada Male as well.

John is now able to understand how to use SUMIFS in Excel. In Summary, SUMIFS remains a key function to apply summation in data, wherever criteria’s are predefined. Keep practicing, and reach us out for any queries.

To get top certifications in Microsoft Excel and build your resume visit here. Also, you can read books listed here to build strong knowledge around Microsoft Excel. 

Watch below our video tutorial:

Looking to practice more with this example? Drop us a note, we will email you the Excel file: