SUMIFS Function in Excel


Home >> How to Use Excel Tutorial >> Excel Formulas List >> SUMIFS Function in Excel



In this tutorial, you will get the understanding of how to use SUMIFS formula in excel. SUMIFS function in excel is something which can be used to sum your digits in this spreadsheet software with more than one particular condition for the digits which want to be processed.


Why We Need to Learn About SUMIFS Formula in Excel?


Calculation process on numeric data in the usage of this spreadsheet tools is often. One kind of process mostly used is addition. In the addition process, sometimes we want to just sum particular digits based on more than one criteria which has been determined before. This is done so we can get the result that we need for next phase of data processing or as the basis of our data analysis.

As one example, when we want to get the picture of how much spending total of a company post in a period of time, then we cannot just add all of the spending numbers that we have in our financial record. To get the result, we must add specific spendings from our financial record, which is just if the spending is done off that post and in the period of time which we want to get a picture from.

For the needs of the process as being illustrated in the case, there is SUMIFS function in excel which we can use in the addition process of digits where there is particular criteria for the ones which want to be added in the process. SUMIFS formula in excel is used by inputting the row/column where the criteria of the data is and also the evaluation condition, inline with the row/column input where the numbers which want to be summed are.


What is SUMIFS Function in Excel?


SUMIFS function in excel is something which can be used to do the addition process of numeric data with more than one criteria. If the case is addition process with just one criteria for the digits, then you can also use SUMIF although SUMIFS formula in excel can also be used if there is only one criteria for the digits. In the writing, SUMIFS function in excel will need the input of all the rows/columns where the evaluation based on the criteria given will be done, criteria to evaluate, and one row/column where the numbers will be added for the result that we want.

A little bit of explanation about the inputs in this formula is as follows:


=SUMIFS(sum_range, criteria_range1, criteria1, …)


Notes:
  • sum_range = the row/column where the numbers of the data entries that fit your criteria want to be summed
  • criteria_range1 = the row/column where the data that will be evaluated with your first criteria
  • criteria1 = the first criteria to evaluate your data
  • … = the other rows/columns where the data to be evaluated and other criteria that you want to be considered as well



How to Use SUMIFS Formula in Excel?


The following parts will explain about how to use SUMIFS formula in excel. In its usage, this is not too different with SUMIF. The only difference is we can input more than one condition in the SUMIFS function in excel.


Using SUMIFS Function in Excel

  1. Type equal sign ( = ) in the cell where you want to put the result from the process

    SUMIFS Function in Excel - Screenshot of Step 1

  2. Type SUMIFS (can be with large and small letters) and open bracket sign after =

    SUMIFS Function in Excel - Screenshot of Step 2

  3. Drag cursor on the cells where the numbers which you want to process based on the digit condition are in and press comma sign ( , )

    SUMIFS Function in Excel - Screenshot of Step 3

  4. Drag cursor on the cells where your first digit condition is and type comma sign. The cell of the digits must be inline vertically/horizontally with your digit cells in which you want to process and should have the same number of cells

    SUMIFS Function in Excel - Screenshot of Step 4

  5. Type your first digit condition then type comma sign. The digit condition must be inside quote sign ( “” ) if the condition is in the form of text or more than/less than condition for the numbers

    SUMIFS Function in Excel - Screenshot of Step 5

  6. Type comma sign after the first digit condition is inputted if there is/are still other condition(s) to be inputted and redo action 4-5

    SUMIFS Function in Excel - Screenshot of Step 6

  7. Type close bracket sign after all the digit conditions have been inputted.

    SUMIFS Function in Excel - Screenshot of Step 7

  8. Press Enter
  9. The process of SUMIFS formula in excel is done!

    SUMIFS Function in Excel - Screenshot of Step 9



Exercise


After you learn how to use SUMIFS formula in excel, you can practice your understanding in using it through this exercise!

Download the file from the following link and answer the questions below. Please download the answer file if you have done the exercise and sure about your results!

Link to download SUMIFS function in excel exercise file to train your understanding:
Download here

Questions

  1. What is the total if the digits component of the addition process have criteria of letter A in the second and fourth columns?
  2. What is the total if the criteria is A for the first column, B for the third column, and C for the fifth column?
  3. What is the total if the condition is A, B, C, C, B for first until fifth column?

Link to download answer file:
Download here


Additional Notes

  • If you want, cell coordinates for the SUMIFS formula in excel input can also be written manually separated for the most top left cell and most bottom right cell by two dots sign ( : ) (Writing example: A1:A30)
  • For the addition process with other methods (using manual method of plus sign ( + ), SUM, and SUMIF) in this spreadsheet tool, you can learn it by visiting other tutorial in this Compute Expert blog!




Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert