SUMIF & SUMIFS Functions in Microsoft Excel

Brief Introduction

            The Microsoft Excel SUMIF Function returns the sum of cells in which cells have the same criteria. Criteria can be applied to dates, numbers, and text using logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose:

Sum numbers in a range which have same criteria.

 Syntax of SUMIF Function:

The syntax of SUMIF Function in Excel is as follows;

=SUMIF (range, criteria, [sum_range])

Return value:

Sum of Number which meets the criteria.

Arguments:

1. Range - The range of cells on which we want to apply the criteria against.
2. Criteria - The criteria (Condition) used to determine which cells to add.
3. Sum_Range - [Optional] The cells to add together, If sum_range is omitted, the cells in the range are added together instead.

Logical Operator type:

When we are using a test with SUMIF Function, we can use any of the following logical operator types,

Comparison operator	Meaning	Example
=	equal to	A1=D1                                       .
>	greater than	A1>D1
>=	greater & or equal to	A1>=D1
<	less than	A1<d1
<=	less than & or equal to	A1<=D1
<>	not equal to	A1<>D1

You can download free the Complete Practice of Concern Topic of Microsoft Excel File, Click on the below download icon,

  

          

              This article explains in simple terms how to use SUMIF Function. It takes a step-by-step approach, & following below contents we will touch in this post.

1. SUMIF Function explanation through various examples
2. Use of SUMIFS Function to return single value from multiple criteria.

Let’s start to learn something new about the SUMIF Function of Microsoft Excel.                

1.  SUMIF Function-Example 1.

             SUMIF Function is used to adds all numbers in a range of cells based on one criterion, as we explain this function through a given below example,

Explanation: - The above specific given information of a company in "Column B, C & D" is regard to the budget line charged in a specific period of time, The column "H" hold the criteria while column "J" hold the result (SUMIF Function), As in the above example, we have targeted the cell "J7" which hold the SUMIF Function while in cell "H7" is our criteria "Furniture".           

In this scenario, we have used a formula like below,

=SUMIF($C$3: $C$11,H7, $D$3: $D$11)

            As we see in the given information there is twice time furniture is charged, first time $ 2,000/- and the second time $ 14,534/- while their sum is $16,534/-, so by using the SUMIF Function in a large data, we get our exact sum result.

Example 2.

               In SUMIF Function, when we use logical operator, for example,">", as shown in below example,

Explanation: - In the above given example, we see that we have to use the logical operator ">", and the cell "J6" hold the formula of SUMIF, ">100" mean that sum the selected range all those numbers which are greater than $100/-, if any cell in the selected range contains the number less than $100/- it will be ignored, so as we see that selected range contain all number greater than $100/- and the accumulative result is $25,104/-.

In this scenario, we have used a formula like below,

=SUMIF(D3:D11,">100")

Example 3.

If in case In SUMIF Function, when we use logical operator, for example, "<", as shown below image,

Explanation: - In the above given example, we see that we have to use the logical operator "<", and the cell "J6" hold the formula of SUMIF, "<400" mean that sum the selected range all those numbers which are less than $400/-, if any cell in the selected range contains the number greater than $400/- it will be ignored, so as we see that selected range contain two number less than $400/- and their accumulative result is $450/-.

In this scenario, we have used a formula like below,

=SUMIF(D3:D11,"<400")

Example 4.

             If in case In SUMIF Function, when we use a logical operator, for example "=", so see in the below example.

Explanation: - In the above given example, we see that we have to use the logical operator "=", and the cell "J6" contains the formula of SUMIF, "=600" mean that sum the selected range all those numbers which are equal to $600/-, if any cell in the selected range contains the number greater or less than $600/- it will be ignored, so as we see that selected range contain one number equal to $600/- and their accumulative result is $600/-.

In this scenario, we have used a formula like below,

=SUMIF(D3:D11,"=600")

Example 5.

             If in case In SUMIF Function, when we use a logical operator, for example "<=", see in the below example.

Explanation: - In the above given example, we see that we have to use the logical operator "<=", and the cell "J6" contains the formula of SUMIF, "<=" mean that sum the selected range all those numbers which are less or equal to the given criteria,  while our give criteria is $1,000/- in cell "H6", if any cell in the selected range contains the number greater than $1,000/- it will be ignored, so as we see that selected the range contains three numbers less or equal to $1,000/- and it's accumulative the result is $1,050/-.

In this scenario, we have used a formula like below,

=SUMIF(D3:D11,"<="&H6)

Example 6.

             If in case In SUMIF Function, when we use a specific word, for example, "Stove", see in below example.

Explanation: - In the above given example, we see that we have to use the specific Word "Stove", and the cell "J6" contains the formula of SUMIF, Word Stove means that sum the selected range all those numbers which are charged in the budget line description of "Stove",  while our give criteria are the word "Stove" if any cell in the selected the range contains the description other than Stove, it will be ignored, so as we see that selected range "C3:C11" contains two numbers by description of Stove and their accumulative result is $1,650/-.

In this scenario, we have used a formula like below,

=SUMIF(C3:C11,"Stove",D3:D11)

2. Use of SUMIFS Function to return a single value from multiple criteria.

             SUMIFS Function most powerful use is to return a single value from multiple criteria, just see in below example,

Explanation: - In the above given example, as we see that there are more than one criteria are used, "budget line & description" are two criteria used in the example, if among these two criteria anyone does not match then it will be ignored and other will be the sum, as just see in the example that "K7" hold the SUMIFS Function and columns "B", "C" & "D" contains the data (Information), and cell "H7" & "I7" contains the two criteria, while cell "K7" contains their result $14,534/-.   

In this scenario, we have used a formula like below,

=SUMIFS($D$3: $D$11,$C$3:$C$11,I7,$B$3:$B$11,H7)

So in example, the "ROW10" matched with given criteria while "Row3" and other "rows" not matched with given criteria therefore only one cell is sum.