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:
- Countif
- CountIfs
- Sumif
Countif Function
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 is used when you want to count cells based on single condition and in single range.
- Countifs is used to count cells based on multiple conditions in multiple ranges.
Syntax of Countif Function
COUNTIF ( range, criteria) wherein
Range = Range to which the condition must apply.
Criteria = the criteria defines which cell should be counted and which not.
How to use Countif Function
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.
Countifs Function
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.
How to use Countifs Function
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.
Sumif function
The syntax for sumif function is
SUMIF(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)
How to use Sumif Function
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.
Related links you will like:
Comments