course bg
EduPristine>Blog>Using Countif and Sumif Function

Using Countif and Sumif Function

February 28, 2015

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.

About Author

avatar EduPristine

EduPristine is a member of Adtalem Global Education (NYSE: ATGE), a global education provider headquartered in the United States. Adtalem is a 3 billion dollars (20,000 crores) company that has about 9 institutions and companies with more than 16,000 employees spread across 145 locations. Adtalem takes pride in training 142,000 degree-seeking students all over the world.The organization's purpose is to empower students to achieve their goals, find success and make inspiring contributions to our global community. EduPristine is one of India's leading training providers in Analytics, Accounting, Finance, Healthcare, and Marketing. Founded in 2008, EduPristine has a strong online platform and network of classrooms across India and caters to self-paced learning and online learning, in addition to classroom learning


Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

Post ID = 71878