COUNT, IF, COUNTIF Formulas Explanation

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:
=IF(C6<70,"F",IF(C6<75,"D",IF(C6<85,"C",IF(C6<95,"B","A"))))
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 numbertext stringcell 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")


Post a Comment

0 Comments