COUNT
The
COUNT function counts the number of cells that contain numbers and counts
numbers within the list of arguments. The syntax is COUNT( value1, value2, …)
Continuing on with our SUM formula from above, let’s not only add up the values
of the range A1:A4, but let’s count how many numbers are included within the
range, i.e. how many cells within the range has a value in it. The formula is
=COUNT(A1:A4). The English translation is count how many cells within the range
has a value in it and display the result.
Notice
that the range is exactly the same as our SUM, A1:A4, which includes four rows.
The value returned in cell A7 is three, because only three of the four rows
have values in them.
If
you are trying to count text, use the COUNTA formula which counts the non-blank
cells.
IF
The
IF function can perform a logical test and return one value for a TRUE result,
and another for a FALSE result. For example, to "pass" scores above
70: =IF(A1>70,"Pass","Fail"). More than one condition
can be tested by nesting IF functions. The IF function can be combined with
logical functions like AND and OR.
Purpose
Test
for a specific condition
Return value
The
values you supply for TRUE or FALSE
Syntax
=IF
(logical_test, [value_if_true], [value_if_false])
Arguments
·
logical_test - A value or logical
expression that can be evaluated as TRUE or FALSE.
·
value_if_true - [optional] The
value to return when logical_test evaluates to TRUE.
·
value_if_false - [optional] The
value to return when logical_test evaluates to FALSE.
Usage notes
Use the IF function to test
for or evaluate certain conditions, and then react differently depending on
whether the test was TRUE or FALSE.
In the example shown, we
want to assign either "Pass" or "Fail" based on a test
score. A passing score is 70 or higher. The formula in D6, copied down, is:
=IF(C6>=70,"Pass","Fail")
Translation: If
the value in C6 is greater than or equal to 70, return "Pass".
Otherwise, return "Fail".
The logical flow this
formula could be reversed. A formula with the same result could be written
like this:
=IF(C6<70,"Fail","Pass")
Translation: If
the value in C6 is less than 70, return "Fail". Otherwise, return
"Pass".
Either formula above, when
copied down the column, will test every score and return the correct
result.
Note:
If you are new to the idea of formula criteria, this article shows many examples.
Nested IF statements
The IF function can be
"nested". A "nested IF" refers to a formula where at least
one IF function is nested inside another in order to test for more conditions
and return more possible results. Each IF statement needs to be carefully "nested"
inside another so that the logic is correct.
For example, the following
formula can be used to assign an grade rather than a pass / fail result:
Up to 64 IF functions can
be nested. However, in general, you should consider other functions, like
VLOOKUP or HLOOKUP for more complex scenarios, because they can handle more
conditions in much more streamlined fashion.
Logical operators
When
you are constructing a test with IF, you can use any of the following logical
operators:
Comparison operator
|
Meaning
|
Example
|
=
|
equal to
|
A1=D1
|
>
|
greater than
|
A1>D1
|
>=
|
greater than or equal to
|
A1>=D1
|
<
|
less than
|
A1<d1<
td=""></d1<>
|
<=
|
less than or equal to
|
A1<=D1
|
<>
|
not equal to
|
A1<>D1
|
COUNTIF
Excel COUNTIF function is used for counting cells within a specified range that
meet a certain criterion, or condition.
For example, you can write a COUNTIF formula to find out how many
cells in your worksheet contain a number greater than or less than the number
you specify. Another typical use of COUNTIF in Excel is for counting cells with
a specific word or starting with a particular letter(s).
The syntax of the COUNTIF function is very simple:
COUNTIF(range,
criteria)
As you see, there are only 2 arguments, both of which are
required:
·
range -
defines one or several cells to count. You put the range in a formula like you
usually do in Excel, e.g. A1:A20.
·
criteria - defines the condition that tells the function which cells
to count. It can be a number, text string, cell
reference or expression. For instance, you can use the
criteria like these: "10", A2, ">=10", "some
text".
And here is the simplest example of Excel COUNTIF function. What
you see in the image below is the list of the best tennis players for the last
14 years. The formula =COUNTIF(C2:C15,"Roger Federer") counts how many times
Roger Federer's name is on the list:
Excel COUNTIF for blank and non-blank cells
These
formula examples demonstrate how you can use the COUNTIF function in Excel to
count the number of empty or non-empty cells in a specified range.
COUNTIF not blank
In
some of other Excel COUNTIF tutorials, you may come across formulas for
counting non-blank cells in Excel similar to this one:
=COUNTIF(range,"*")
But
the fact is, the above formula counts only cells containing any text
values, meaning that cells with dates and numbers will be treated as
blank cells and not included in the count!
If you
need a universal COUNTIF formula for counting all non-blank cells in a specified range, here you go:
=COUNTIF(range,"<>"&"")
This
formula works correctly with all value types - text, dates and numbers - as
you can see in the screenshot below.
COUNTIF blank
If you
want the opposite, i.e. count blank cells in a certain range, you should adhere
to the same approach - use a formula with a wildcard character for text values
or another one (with the "" criteria) to count all empty cells.
Formula
to count cells not containing any text:
=COUNTIF(range,"<>"&"*")
Translated
into plain English, the criteria used in the above formula
("<>"&"*") means to find cells not equal to *,
i.e. not containing any text in the specified range.
Universal
COUNTIF formula for blanks (all value types):
=COUNTIF(range,"")
The
above formula correctly handles numbers, dates and text values. For example,
the formula
=COUNTIF(C2:C11,"")
returns the number of all empty cells in the range C2:C11.
Note. Please
be aware that Microsoft Excel provides another function for counting blank
cells
=COUNTBLANK(range)
. For instance, the below formulas will produce exactly the
same results as the COUNTIF formulas you see in the screenshot above:
Count
blanks:
=COUNTBLANK(C2:C11)
Count
non-blanks:
=ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)
Also,
please keep in mind that both
=COUNTIF(range,"")
and =COUNTBLANK(range)
count cells with formulas that only look empty. If you do
not want to treat such cells as blanks, use this formula instead: =ROWS(C2:С11)*COLUMNS(C2:С11)-COUNTIF(C2:С11,"<>"&"")
.COUNTIF greater than, less than or equal to
To
count cells with values greater than, less
than or equal
to the number you specify,
you simply add a corresponding operator to the criteria, as shown in the table
below.
Please
pay attention that in COUNTIF formulas, an operator with a number are always enclosed
in quotes.
Criteria
|
Formula Example
|
Description
|
Count
if greater than
|
=COUNTIF(A2:A10,">5")
|
Count
cells where value is greater than 5.
|
Count
if less than
|
=COUNTIF(A2:A10,"<5")
|
Count
cells with values less than 5.
|
Count
if equal to
|
=COUNTIF(A2:A10,"=5")
|
Count
cells where value is equal to 5.
|
Count
if not equal to
|
=COUNTIF(A2:A10,"<>5")
|
Count
cells where value is not equal to 5.
|
Count
if greater than or equal to
|
=COUNTIF(C2:C8,">=5")
|
Count
cells where value is greater than or equal to 5.
|
Count
if less than or equal to
|
=COUNTIF(C2:C8,"<=5")
|
Count
cells where value is less than or equal to 5.
|
You
can also use all of the above formulas to count cells based on another cell value, you
will just need to replace the number in the criteria with a cell reference.
Using Excel COUNTIF function with dates
If you
want to count cells with dates that are greater than, less than or equal to the
date you specify or date in another cell, you proceed in the already familiar
way using formulas similar to the ones we discussed a moment ago. All of the
above formulas work for dates as well as for numbers. Let me give you just a
few examples:
Criteria
|
Formula Example
|
Description
|
Count
dates equal to the specified date.
|
=COUNTIF(B2:B10,"6/1/2014")
|
Counts
the number of cells in the range B2:B10 with the date 1-Jun-2014.
|
Count
dates greater than or equal to another date.
|
=COUNTIF(B2:B10,">=6/1/2014")
|
Count
the number of cells in the range B2:B10 with a date greater than or equal to
6/1/2014.
|
Count
dates greater than or equal to a date in another cell, minus x days.
|
=COUNTIF(B2:B10,">="&B2-"7")
|
Count
the number of cells in the range B2:B10 with a date greater than or equal to
the date in B2 minus 7 days.
|
Apart
from these common usages, you can utilize the COUNTIF function in conjunction
with specific Excel Date and Time functions such as TODAY() to count cells
based on the current date.
Criteria
|
Formula Example
|
Count
dates equal to the current date.
|
=COUNTIF(A2:A10,TODAY())
|
Count
dates prior to the current date, i.e. less than today.
|
=COUNTIF(A2:A10,"<"&TODAY())
|
Count
dates after the current date, i.e. greater than today.
|
=COUNTIF(A2:A10,">"&TODAY())
|
Count
dates that are due in a week.
|
=COUNTIF(A2:A10,"="&TODAY()+7)
|
Count
dates in a specific date range.
|
=COUNTIF(B2:B10,
">=6/7/2014")-COUNTIF(B2:B10, ">6/1/2014")
|
0 Comments