Using Count, Countif, Countifs formula in excel

COUNTIF function

The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify. For example, you can count all the cells that start with a certain letter, or you can count all the cells that contain a number that is larger or smaller than a number you specify. For example, suppose you have a worksheet that contains a list of tasks in column A, and the first name of the person assigned to each task in column B. You can use the COUNTIF function to count how many times a person's name appears in column B and, in that way, determine how many tasks are assigned to that person. For example: =COUNTIF(B2:B25,"Nancy")

Syntax

COUNTIF(range, criteria)

The COUNTIF function syntax has the following arguments:

• range Required. One or more cells to count, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
• criteria Required. A number, expression, cell reference, or text string that defines which cells will be counted. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32".

COUNTIFS function

Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.

The difference is that the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. So, the aim of this tutorial is to help you figure out the most efficient formula for each particular task.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)

• 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 can be expressed as 10, "<=32", A6, "sweets".
• [criteria_range2, criteria2]... - these are additional ranges and their associated criteria, optional. You can specify up to 127 range/criteria pairs in your formulas.

In fact, you don't have to remember the syntax of the COUNTIF function by heart. As well as with any other formula, Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.

Example 1: You can count how many numbers are between the numbers you specify in two ways - using a COUNTIFS function or a difference between two COUNTIF functions.

Example 2: Suppose you have a list of products indicating how many items are sold, delivered and in stock. You can use the following formulas to count the numbers of products, at least one item of which has been sold and delivered, i.e. if the values in columns B and D are greater than 0:

Share on Facebook Share on Twitter Share on Google Plus

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.