Want to Enable Key Features?

COUNTIF Function | Excel

Apr 28, 2017

Excel provides conditional functions which allow the user to count all the cells in a range that meets single or multiple criteria. The following paragraphes will explain two types of these functions called "Countif" and "Countifs". They could check single and multiple criteria, respectively. This tutorial assumes you have a working knowledge of Average & If functions.

COUNTIF

The Countif function or statement is used to count all the cells in a range that meets single criteria. Typically, this function consists of two arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=COUNTIF(range,criteria)

Where,

• â— RANGE is one or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• â— CRITERIA is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "balls", or B4.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formulaâ€™s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of COUNTIF function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select COUNTIF from the displayed functions.

• iii. The next dialog box will appear and the two arguments can be seen within the box.

• iv. Click in RANGE text box.
• v. Select the range of cells you wish to check and count.
• vi. Click in the CRITERIA box, either, type criteria directly in the box or select a cell that contains the value to check against your RANGE and Click OK.

In next example, returns the count of B2:B5 where the corresponding value in column A contains a certain fruit name:

Countifs

Countifs function or statement is used to count all the cells in a range that meets multiple criteria. Typically, this function consists of more than or equal to three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=COUNTIFS(range1, criteria1,range2, criteria2â€¦)

Where,

• â— RANGE1, RANGE2, â€¦ are 1 to 127 ranges in which to evaluate the associated criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• â— CRITERIA1, CRITERIA2, â€¦are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "balls", or B4.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formulaâ€™s ribbon, under heading of more functions in statistical. The Function Library allow to view the above syntax (details) of COUNTIFS function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the MORE FUNCTIONS button, go to STATISTICAL and select COUNTIFS from the displayed functions.
• iii. The next dialog box will appear and more than three arguments can be seen within the box.

• vi. Click in the CRITERIA_RANGE1 box select the range of cells that you wish to count.
• v. Click in the CRITERIA1 text box and type in the criteria to measure against your CRITERIA_RANGE1.
• vi. Repeat step vi and vi to enter multiple criteria, criteria_range2, range3 etc, more text boxes will appear after filling-in each criteria_range and criteria. Click OK when all ranges and criterias have been entered.

• â˜º Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell.
• â˜º If criteria is an empty cell, COUNTIFS treats it as a 0 value.
• â˜º Wildcard characters, question mark (?) and asterisk (*), could be used in criteria. Find more explanation on "The Use of Wildcard Characters" in Sumif Tutorial.

In next example, the number of times for saler(s) who were able to exceed quota are calculated matching multipile criteria for several products.

Keywords:

excel tutorial, excel advanced, countif, countifs, conditional function, formula

Adam is a freelance web designer, recreational software developer, author and CEO of infoapper.com . Also, has passion to learn and transfer the know-how of every valuable thing.
Views: 250
0 Comment Guest
Recommend 0
Be the first to say something...

Ask questions and Share knowledge with Community

Find below recent posts for automation solutions with questions and answers by community. You can search in past threads or post new question about your assignment with detailed description, and always could mark your question as request. Sharing knowledge are highly appreciated by answering on others questions, and in return awards will be decided.

#
Recent Questions
*
i need to know how to convert 723,413,000 sq mm into mm2.
118
1
0
*
How to convert kg/m3 to kn/m3
116
1
0

View More

Article Categories
× Close
Results: