Download Counting Cells in Excel using COUNT, COUNTA, COUNTIF and COUNTIFS Functions and more Exercises MS Microsoft Excel skills in PDF only on Docsity!
Excel COUNT function - count cells with numbers
You use the COUNT function in Excel to count the number of cells that contain numerical values.
The syntax of the Excel COUNT function is as follows:
COUNT(value1, [value2], …)
Where value1, value2, etc. are cell references or ranges within which you want to count cells with numbers.
In Excel 365 - 2007, the COUNT function accepts up to 255 arguments. In earlier Excel versions, you can supply up to 30 values.
For example, the following formula returns the total number of numeric cells in range A1:A100:
=COUNT(A1:A100)
Note. In the internal Excel system, dates are stored as serial numbers and therefore the Excel COUNT function counts dates and times as well.
Using COUNT function in Excel - things to remember
Below are the two simple rules by which the Excel COUNT function works.
- If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
- If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like "5").
For example, the following COUNT formula returns 4, because the following values are counted: 1, "2", 1/1/2016, and TRUE.
=COUNT(1; "apples"; "2"; 1/1/2016, TRUE)
Excel COUNT formula examples
And here are a few more examples of using the COUNT function in Excel on different values.
To count cells with numeric values in one range , use a simple count formula like
=COUNT(A2:A10)
The following screenshot demonstrates which types of data are counted and which are ignored:
To count several non-contiguous ranges , supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:
=COUNT(B2:B7 ; D2:D7)
Tips:
- If you want to count numbers that meet certain criteria , use either the COUNTIF or COUNTIFS function.
- If apart from numbers, you also want to count cells with text, logical values and errors, use the COUNTA function, which leads us right to the next section of this tutorial.
Excel COUNTA function - count non-blank cells
Tip. If you just want a quick count of non-blank cells in a selected range , simply have a look at Status Bar at the bottom right corner of your Excel window:
Other ways to count cells in Excel
Aside from COUNT and COUNTA, Microsoft Excel provide a few other functions to count cells. Below you will discuss 3 most common use cases.
Count cells that meet one condition (COUNTIF)
The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:
COUNTIF(range; criteria)
In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.
For example, to count how many cells in range A2:A15 are " Apples ", you use the following COUNTIF formula:
=COUNTIF(A2:A15; "apples")
Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:
Count cells that match several criteria (COUNTIFS)
The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:
COUNTIFS(criteria_range1; criteria1; [criteria_range2, criteria2]…)
The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010 - 365.
For example, to count how many " Apples " (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:
=COUNTIFS(A2:A15,"apples", B2:B15,">=200")
To make your COUNTIFS formula more versatile, you can supply cell references as the criteria:
- If the criteria is a reference to an empty cell , the COUNTIFS function treats it as a zero value (0).
- You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.
How to use COUNTIFS and COUNTIF with multiple
criteria in Excel
Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to evaluate multiple conditions.
How to count cells with multiple criteria (AND logic)
Formula 1. COUNTIFS formula with multiple criteria
Suppose you have a product list like shown in the screenshot below. You want to get a count of items that are in stock (value in column B is greater than 0) but have not been sold yet (value is column C is equal to 0).
The task can be accomplished by using this formula:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
And the count is 2 (" Cherries " and " Lemons "):
Formula 2. COUNTIFS formula with two criteria
When you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually.
For example, here's the right formula to count items that have 0 both in column B and column C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
This COUNTIFS formula returns 1 because only " Grapes " have "0" value in both columns.
Using a simpler formula with a single criteria_range like COUNTIFS(B2:C7,"=0") would yield a different result - the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).
How to count cells with multiple criteria (OR logic)
As you have seen in the above examples, counting cells that meet all of the specified criteria is easy because the COUNTIFS function is designed to work this way.
But what if you want to count cells for which at least one of the specified conditions is TRUE , i.e. based on the OR logic? Overall, there are two ways to do this - by adding up several COUNTIF formulas or using a SUM COUNTIFS formula with an array constant.
Formula 1. Add up two or more COUNTIF or COUNITFS formulas
In the table below, supposing you want to count orders with the " Cancelled " and " Pending " status. To have it doen, you can simply write 2 regular Countif formulas and add up the results:
=COUNTIF($C$2:$C$11,"Cancelled") + COUNTIF($C$2:$C$11,"Pending")
Formula 1. COUNTIFS to count cells between two numbers
To find out how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10, use this formula:
=COUNTIFS(C2:C10,">5", C2:C10,"<10")
To include 5 and 10 in the count, use the "greater than or equal to" and "less than or equal to" operators:
=COUNTIFS(B2:B10,">=5", B2:B10,"<=10")
Formula 2. COUNTIF formulas to count numbers between X and Y
The same result can be achieved by subtracting one Countif formula from another. The first one counts how many numbers are greater than the lower bound value (5 in this example). The second formula returns the count of numbers that are greater than the upper bound value (10 in this case). The difference between the first and second number is the result you are looking for.
- =COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10") - counts how many numbers greater than 5 and less than 10 are in the range C2:C10. This formula will return the same count as shown in the screenshot above.
- =COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10") - the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.
How to use cell references in COUNTIFS formulas
When using logical operators such as ">", "<", "<=" or ">=" together with cell references in your Excel COUNTIFS formulas, remember to enclose the operator in "double quotes" and add an ampersand (&) before a cell reference to construct a text string.
In a sample dataset below, let's count " Apples " orders with amount greater than $200. With criteria_range1 in cells A2:A11 and criteria_range2 in B2:B11, you can use this formula:
=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")
Or, you can input your criteria values in certain cells, say F1 and F2, and reference those cells in your formula:
=COUNTIFS($A$2:$A$11, $F$1, $B$2:$B$11, ">"&$F$2)
Please notice the use of absolute cell references both in the criteria and criteria_range arguments, which prevents the formula from being broken when copied to other cells.
COUNTIFS and COUNTIF with multiple criteria for dates
The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.
Example 1. Count dates in a specific date range
To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.
For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")
=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")
This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())
This is how you count cells with multiple criteria in Excel. I hope you will find these examples helpful.
