Notice thatcriteriais given as a text value in double quotes (""). In this syntax,logical operatorsare joined with numeric values and provided as text. If the value in A1 is changed to a different number, COUNTIF will return a new result. COUNTIF can be used to count cells that…

In other words, to count values that are “out of range”. You might at first think to use theCOUNTIFS functionwith two criteria. Instead, we need OR logic. When added together these two results correctly handle the required logic: less than I5 OR greater than J5. This is an example of usingboolean algebrawith addition (+), which creates OR logic. The formula used to highlight the out-of-range values above is: More detailshere.

The simplest way to do this is with theCOUNTIF function, as explained below. Not equal to In Excel, theoperatorfor not equal to is “<>”. The word “red” can appear in any combination of uppercase or lowercase letters. COUNTIF can be used to count cells that…

Explanation First, a little context. That’s exactly what the formula on this page does. Then we use adouble negative(–) to coerce TRUE to 1 and FALSE to zero. SUMPRODUCT function TheSUMPRODUCT functionis designed to multiply and then sum multiple arrays. TheCOUNTIF function, given the named rangeexcludewill return three counts, one for each item in the list. SUMPRODUCT adds up the total, and this number is subtracted from the count of all non-empty cells....

This problem can be solved with the COUNTIFS function or the SUMPRODUCT function, as explained below. Not equal to The not equal tooperatorin Excel is <>. The second formula returns FALSE since A1 is equal to 10. We want to count cells where the color isnotred or blue. The conditions given to COUNTIFS are supplied with range/criteriapairs, and can uselogical operators. The key in this case is to use the “not equals” operator, which is <>....

The counts in column C make it easy to quickly check results. Note the greater thanoperator(>) is enclosed in double quotes ("") andconcatenatedto F4. LEN will also count characters in numbers, but number formatting is not included. N Function The Excel N function returns a number when given a value. The N function can be used to convert TRUE and FALSE to 1 and 0 respectively. When given a text value, the N function returns zero....

Explanation In this example, the goal is to count cells in arangethat are blank. Counting blank cells in Excel can be tricky because cells canlookblank even when they are not actually empty. The article below explains three different approaches. COUNTBLANK function The simplest way to count empty cells in a range is to use theCOUNTBLANK function. COUNTBLANK is fully automatic, so there is nothing to configure. The secondrange/criteriapair selects empty cells....

There are several ways to go about this task, depending on your needs. The article below explains different approaches. COUNTA function While theCOUNT functiononly counts numbers, theCOUNTA functioncounts both numbers and text. This means you could use COUNTA as a simple way to count cells that are not blank. COUNTA is fully automatic, so there is nothing to configure. The secondrange/criteriapair selects cells that are not empty. The result from COUNTIFS is 4, since there are 4 cells in Group A that are not empty....

), asterisk(*), or tilde (~). A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. The tilde (~) is an escape character to matchliteralwildcards that may appear indata. In this example, we only need to use an asterisk (*). Notice that COUNTIF isnotcase-sensitive. For example, to make the formula case-sensitive, we can add theEXACT function. However, one quirk of the FIND function is that it will return a #VALUE!...

Because we give FINDmultiplevalues in thewithin_textargument, it returnsmultipleresults. represents a value in B5:B15 that does not contain “ABC”. Looking more closely, we can see that FIND found “ABC” in 4 cells out of 11. When the text is not found, FIND returns a #VALUE error.

For convenience, the range B5:B15 isnameddata. Note: The main formula described on this page is case-sensitive because theFIND functionis case-sensitive. If you want a formula that is not case-sensitive, you might substitute theSEARCH functionfor the FIND function. SUMPRODUCT formula One way to solve this problem is to use theSUMPRODUCT functionwithISNUMBER+FIND. The formula in E5 is: This formula is based on aformula explained herethat finds text in a cell. If the text is not found, FIND returns a #VALUE!...

The article below explains several different approaches, depending on your needs. For background, this article:Excel Formula Errors. But COUNTIF is in a group ofeight functions that have some quirks, and this is one of them. One limitation of this approach is that there is no simple way to countall error typeswith a single formula. However, if you take a stab at enter this formula, Excel won’t let you. One solution is to use SUMPRODUCT with ISERROR, as explained below....

This problem can be solved with a formula based on the SUMPRODUCT and ISFORMULA functions, as explained below. ISFORMULA function TheISFORMULA functionreturns TRUE if a cell contains a formula, and FALSE if not. Each TRUE value represents a cell thatdoescontain a formula. The next step is to convert the TRUE and FALSE values to 1s and 0s. To learn more about this topic, seeWhy SUMPRODUCT? When a cell contains a formula ISFORMULA will return TRUE regardless of the formula’s output or error conditions....