Function Sumif()

SumIf()

Sumif() is a variant of Sum() function and it calcuates the sum of a given range, of only those cells (within the range) which meet the criteria mentioned in the function.


Syntax:

= SumIf (Range, Criteria, [SumRange])

Range = it specifies the range of cells on which the creterion is to be tested. Also, in case the SumRange is not provided in the function, the sum is also calculated on this range
SumRange = this range specifies the cells on which the sum is calculated. This is optional.

Note: The Sumif() has 2 formats - one with the Sumrange and one without it. In the first format, the range on which the criterion is to be tested is same as the one for which the sum is to be calcuated. In the second format, the 2 ranges are different. It will become much easier to understand through the below examples.


Example:

Example 1: In the below image, the criteria for calculating the sum is - "the cell value should be more than or equal to 15,000"

The sumif() function checks the range D2:D6 for the cells which contain value more than or equal to 15,000 and then add up those cells which meet the criteria. Note that the condition has been checked on the same range (i.e. - D2:D6) on which the sum is calculated. hence, the SumRange argument is not required in this function.




Example 2: Let us now assume that we need to calculate the total salary of all the 'Analysts' as given in the above image. For this, we will use the second format of SumIf() function.



In the image above, the cells which meet the condition, i.e. cells which have 'Analyst' in the corresponding range, gets added up in the total. The cells highlighted with a tick mark are the ones which meet the criteria.
Share on Google Plus

About Nifty Guru

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment

Tuesday, May 26, 2009

Function Sumif()

SumIf()

Sumif() is a variant of Sum() function and it calcuates the sum of a given range, of only those cells (within the range) which meet the criteria mentioned in the function.


Syntax:

= SumIf (Range, Criteria, [SumRange])

Range = it specifies the range of cells on which the creterion is to be tested. Also, in case the SumRange is not provided in the function, the sum is also calculated on this range
SumRange = this range specifies the cells on which the sum is calculated. This is optional.

Note: The Sumif() has 2 formats - one with the Sumrange and one without it. In the first format, the range on which the criterion is to be tested is same as the one for which the sum is to be calcuated. In the second format, the 2 ranges are different. It will become much easier to understand through the below examples.


Example:

Example 1: In the below image, the criteria for calculating the sum is - "the cell value should be more than or equal to 15,000"

The sumif() function checks the range D2:D6 for the cells which contain value more than or equal to 15,000 and then add up those cells which meet the criteria. Note that the condition has been checked on the same range (i.e. - D2:D6) on which the sum is calculated. hence, the SumRange argument is not required in this function.




Example 2: Let us now assume that we need to calculate the total salary of all the 'Analysts' as given in the above image. For this, we will use the second format of SumIf() function.



In the image above, the cells which meet the condition, i.e. cells which have 'Analyst' in the corresponding range, gets added up in the total. The cells highlighted with a tick mark are the ones which meet the criteria.

Function Sumif()SocialTwist Tell-a-Friend

0 comments:

Post a Comment