If you are using excel very often at your workplace, then using the countif and sumif function may reduce a lot of your work load. After reading this article we are very sure that you will be clear with following functions:
Very often people get confused between countif and countifs function, as they both seem to be alike and the purpose of using them is also same. The only difference between these two functions is
COUNTIF ( range, criteria) wherein
Range = Range to which the condition must apply.
Criteria = the criteria defines which cell should be counted and which not.
1. In our example below, we want to count the number of students who scored more than 50 marks in Subject 1. Since it is a single criteria, we shall use the function of Countif in B11.
2. We shall select the range of subject 1 (B2:B9) and in criteria we shall mention more than 50(make sure it is in “ “)
3. Press enter and you shall get your cell count in B11.
Syntax of Countif Function
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) wherein
criteria_range1 - defines the first range to which the first condition (criteria1) shall be applied, required.
criteria1 - sets the condition in the form of a number, cell reference, text string, expression or another Excel function, required. The criteria defines which cells shall be counted and which not.
[criteria_range2, criteria2]… - these are additional ranges and their associated criteria, optional. You can specify up to 127 range/criteria pairs in your formulas.
1. In this example, we shall count the number of students who have scored more than 50 marks in Subject 1 as well as Subject 3 and since, it is more than 1 condition we shall use the Countifs Function.
2. Since we want to know the number of students scoring 50 in more than 2 subjects, we shall be selecting 2 table ranges and shall mention 2 criteria (see the formula).
3. Press enter and you shall get your result.
The syntax for sumif function isSUMIF(range, criteria, [sum_range]) wherein Range - the range of cells to be evaluated by your criteria Criteria - the condition that must be met Sum_range - the cells to sum if the condition is met (optional)
1. In our case we want to find out the sum of the months where the sales are above 5000 in London. Since it is a single condition we shall use the function of sumif.
2. We shall select the range of London (B2:B13) and in criteria we shall mention more than 5000(make sure it is in “ “)
3. On pressing enter you will see your answer
If you have any questions or suggestions then do let us know by commenting in the comments box below.
Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc
CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.
Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to email@example.com and we will rectify it.
2015 © Edupristine. ALL Rights Reserved.