Tag: Excel NOT

How to Count Cells that do not Contain Errors in Excel

This post will guide you how to count the number of cells that do not contain errors within a range of cells using a formula in Excel 2013/2016 or Excel office 365. How do I count the number of cells without any errors using a simple formula in Excel.

1. Count Number of Cells that do Not Contain Errors using Formula

You can use the SUMPRODUCT function in combining with NOT and ISERROR functions to count cells in a specified range of cells that do not contain errors

The below is a generic formula to count the number of cells that do not contain errors:

=SUMPRODUCT(--NOT(ISERR(range)))

Supposing that you wish to count the number of cells in range B1:B6 that do not contain errors, and you can use the following formula:

=SUMPRODUCT(--NOT(ISERR(B1:B6)))
count cell do not contain error1

LET’S SEE THAT HOW THIS FORMULA WORKS:

= NOT(ISERR(B1:B6))

count cell do not contain error2

The NOT and ISERROR functions can be used to identify the cells that do not contain an error. And it returns an array result with TRUE or FALSE values. The TRUE values indicate cells without errors, and FALSE values indicate cells with error values.

The array result is like this:

{TRUE;TRUE;TRUE;FALSE;TRUE;FALSE}
count cell do not contain error3

= –NOT(ISERR(B1:B6))

count cell do not contain error4

Then you still need to convert the above array result to their numeric values, 1 and 0, so that the SUMPRODUCT function can calculate the sum of products of corresponding numbers. And you can do this with a double negative operator. And the final array result is like this:

{1;1;1;0;1;0}
count cell do not contain error5

The SUMPRODUCT function sums the items in the above array and return the final result.

You can also use the SUM function to count the cell that do not contain errors in range B1:B6. And the generic formula is similar with the above formula. It need to be entered as an array formula through pressing CTRL +SHIFT + Enter to convert it as array formula. The formula looks like this:

{=SUM(--NOT(ISERROR(B1:B6)))}
count cell do not contain error6

2. Count Cells that do not Contain Errors using User Defined Function with VBA Code

If you want to count the number of cells that do not contain errors in a range, you can write a user defined function with VBA code. Here are the steps to create and run the function:

Step1: Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

Step2: Click Insert > Module, and paste the following code in the Module Window.

How to Count Cells that do not Contain Errors in Excel vba1.png
Function CountNonErrors_ExcelHow(rng As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    For Each cell In rng
        If Not IsError(cell.Value) Then
            count = count + 1
        End If
    Next cell
    CountNonErrors_ExcelHow = count
End Function

Step3: Save and close the window.

Step4: In a blank cell, enter the formula:

=CountNonErrors_ExcelHow(B1:B6)

Where B1:B6 is the range you want to count.

Step5: Press Enter to get the result. This function will return the number of cells that do not contain errors in the specified range.

How to Count Cells that do not Contain Errors in Excel vba2.png

3. Video: Count Cells that do not Contain Errors in Excel

This video will show you how to count cells that do not contain errors in Excel using a simple formula and a VBA code.

4. Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

How to Count Cells that Contain only numbers in Excel

This post will guide you how to count the number of cells that only contain numbers within a range of cells using a formula in Excel 2013/2016.

Count Number of Cells that Contain Numbers

Assuming that you have a data list in the range of cells B1:B6, and you want count the number of cells containing only numbers, and write the result in cell D1.You can use a formula based on the COUNT function  Like this:

=COUNT (B1:B6)

The Syntax of COUNT function is as below:

=COUNT(value1,value2…)

Note: Value1 is reequired, which can be a cell range, a row or a column.

You would see that the above formula COUNT function should be count number of cells contain only number within the range B1:B6.

count cells that contain number1

If you want to count the number of cells that does not contain numbers, and you can use a formula based on the SUMPRODUCT function and the ISNUMBER function, like this:

=SUMPRODUCT(–NOT(ISNUMBER(B1:B6)))

Let’s see that how this formula works:

=ISNUMBER(B1:B6)

The ISNUMBER function can be used to search number in the range B1:B6 and returns TRUE or FALSE values. The above function would return an array result;

{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}

=NOT(ISNUMBER(B1:B6))

The NOT function will convert the above array result in reverse. And the result still is an array like this:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}

Then you need to use double negative operator convert the above result array into 1 and 0, like this:

{0;1;0;0;1;0}

The SUMPRODUCT function will be used to add the array and return final result.

count cells that contain number2

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…
  • Excel NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…

Excel NOT Function

This post will guide you how to use Excel NOT function with syntax and examples in Microsoft excel.

Description

The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. So you can use the NOT function to reverse the behavior of an expression or another function.

The NOT function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.

The NOT function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the NOT function is as below:

=NOT(logical)

Where the NOT function argument is:

  • Logical -This is a required argument.  A logical value or expression that can be evaluated as TRUE or FALSE. And the supplied logical argument must be a logical or numeric value.

Excel NOT Function Examples

The below examples will show you how to use Excel NOT Function to get the opposite to a supplied logical value or expression.

1# =NOT(B1>20)

excel not examples1

If the Cell B1 value is greater than 20, then this formula will return FALSE. And if the Cell B1 value is smaller than 20, this formula will return TRUE

Related Functions

  • Excel OR function
    The Excel OR function used to test multiple conditions and returns TRUE if any of the conditions are TRUE. Otherwise, it will return FALSE. The syntax of the OR function is as below:=OR(logical1, [logical2], …)…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….

Related Formulas

  • Excel IF formula with NOT logical function
    If you want to check several test conditions in an excel formula,then take different actions. You can use NOT function in combination with the AND or OR logical function in excel IF function….

Excel IF formula with NOT logical function

This post will guide you how to use excel if formula with NOT logical function in Microsoft excel.

NOT Logical Function

The syntax of NOT logical function is as follow:

=NOT(logical_value)

The NOT logical function will return the reversed logical value. If the logical_value is TRUE, then FALSE is returned. If the logical_value is FALSE, then TRUE is returned. Let’s see the below two examples:

=NOT(FALSE)
=NOT(2+1=3)

Both of the above excel formulas will return TRUE.

Excel IF Formula Combining With NOT

If you want to check several test conditions in an excel formula,then take different actions. You can use NOT function in combination with the AND or OR logical function in excel IF function.

Let’s see the following excel if formula:

=IF(NOT(OR(B1="red",B1="black")),TRUE,FALSE)

In the above formula, the excel formula will exclude both red and blank colors. It will check cells in column B if the values is NOT red or blank. TRUE or FALSE will be returned.

Excel formula using IF NOT function1

Actually, we also can use the Not equal to (<>) logical operator to achieve the same requirement , For example, we can write down the following excel IF formula to reflect the above logic test.

=IF(AND(B1<>”red”,B1<>”black”)),TRUE,FALSE)

Excel formula using IF NOT function2

NOT Function combining with ISBLANK function

As you know, the function ISBLANK(B1) will return TRUE if the cell B1 is blank. If we use NOT function to enclose ISBLANK(B1) function, it will reverse the result, if the cell is blank, then returns FALSE.

Let’s create a nested IF statement with the NOT and ISBLANK functions as follow:

=IF(NOT(ISBLANK(B1)),B1*5,””)

Excel formula using IF NOT function3

For above IF statement, IF the cell B1 is not blank, then multiply the number in B1 by 5.we can enter the above formula into cell C1, and then drag the Fill Handle to the range C1:C3.it will apply for the other cells for this formula.

Related Formulas

Related Functions

  • Excel OR function
    The Excel OR function returns TRUE if any of the conditions are TRUE in logic test. Otherwise, it returns FALSE.
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…) …
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….
  • Excel nested if function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions…
  • Excel ISBLANK function
    The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…
  • Excel NOT function
    The Excel NOT function returns the opposite of a given logical or Boolean value. For example, if you supplied with the value TRUE, the NOT function will return FALSE; If you supplied with the value FALSE, and the NOT function will TRUE. The syntax of the NOT function is as below:=NOT(logical)…