Share with your network!

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.

countif function in excel

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 “ “)

Countif formula

3. Press enter and you shall get your cell count in B11.

how to use countif function in excel

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.

using countifs with multiple criteria

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).

Countifs formula in excel

3. Press enter and you shall get your result.

Using countifs function in Excel

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.

Sumif function in Excel

2. We shall select the range of London (B2:B13) and in criteria we shall mention more than 5000(make sure it is in “ “)

Sumif formula in Excel

3. On pressing enter you will see your answer

How to use sumif formula in excel

If you have any questions or suggestions then do let us know by commenting in the comments box below.