Explanation In this example the goal is to count numbers longer than 15 digits with a formula. TheCOUNTIF functionmay seem like this logical choice. you could see this problem in the worksheet below. All counts in column D areincorrect. Each number in column B is unique, yet the count returned by COUNTIF suggests the numbers are duplicates. This problem is related to how Excel handles numbers. The counting problem mentioned above arises from this limit....

SUMPRODUCT function TheSUMPRODUCT functionis a versatile function thathandles array operations nativelywithout any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays. The formula in G7 is: This formula returns 2, since there are two non-matching cells.

The left part tests column headers, and the right tests values. The result from MATCH is anarraycomposed of #N/A errors or numbers. The numbers indicate matched positions: There are 6 items in this array because we are testing 6 columns. The numbers represent matched columns and errors represent columns that do not match. This completes the column matching logic. When the logic is separated into separate arrays, anadditional stepmust be taken to convert to 1s and 0s....

This problem can be solved with the COUNTIF function or the MATCH function, as explained below. Both approaches work well. The advantage of the MATCH approach is that it will work witharraysorranges. The COUNTIF function is limited to ranges only, likeother functions in this group. The 0s indicate names in B5:B16 that don’t appear in D5:D12. In other words, we need to convert the 1s to 0s and the 0s to 1s....

Explanation In this example, the goal is to count non-blank dates in column D by group. All data is anExcel Tablenameddatain the range B5:D16. This problem can be solved with theCOUNTIFS function, as explained below. COUNTIFS function The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS accepts ranges and criteria in pairs. Because there are 5 rows in the table where the group is “A”, COUNTIFS returns 5....

Specifically, we want to count males that are not in group A or B. All data is in anExcel Tablenameddatain the range B5:D15. This problem can be solved with the COUNTIFS function or the SUMPRODUCT function. Both approaches are explained below. Next, we need to exclude group “A”: This formula returns 5. Notice we use the not equal tooperator(<>) enclosed in double quotes. This formula returns 2. As more exclusions are added however, the syntax gets more cumbersome, because each new exclusion requires anotherrange/criteriapair....

However, for reasons explained below, COUNTIF won’t work. Instead, you could use the SUMPRODUCT andBoolean logic. See below for a full explanation. You would think you could use the COUNTIF function with the question mark (?) As a result, COUNTIF will never find a matching number and the result will always be zero. This happens because COUNTIF is in agroup of eight functions that require an actual rangeforrangearguments. To do this, we use adouble negative(–)....

A simple way to solve this problem is with the COUNTIFS function. Both approaches are explained below. Notice in the formulas above, we are hardcoding the numbers into the formula. For a detailed overview of concatenation, seeHow to concatenate in Excel. Notice that by design, the FREQUENCY function always returns a count forone more binthan is provided. With a Pivot Table APivot Tableis another way to solve this problem. Seethis video for a similar example....

This approach is described below. Create the array The first step is to split the text string into an array of characters. Where it fails, we get a #VALUE! error: The COUNT function then counts the numbers in the array and returns a final result of 2. TheROW functionthen evaluates the text and returns a reference. Note this is anarray formulaand must be entered with control + shift + enter in older versions of Excel....

Instead, it’s possible for you to use the SUMPRODUCT andBoolean logic. See below for a full explanation. As a result, COUNTIF will never find a matching number and the result will always be zero. In other words, you’ve got the option to’t use anarray operationin place of arangeargument. To do this, we use adouble negative(–). In this formula, note we are hardcoding the value “25” and the length of 2....

Explanation In this example, the goal is to count numbers that contain leading zeros. The challenge is that Excel can be finicky with leading zeros. Technically, the values in B5:B16 aretext, as is the value in E5. However, sometimes text values that contain numbers are converted tonumericvalues as they go through Excel’s calculation engine. When this happens, the leading zeros will be silently removed, which can cause an incorrect result....

The workbook shown in the example has four worksheets in total. This formula works but becomes cumbersome as the number of sheets increases. One of these quirks is that you might’t use a 3D reference for therangeargument. This approach helps streamline the formula as explained below. INDIRECT will evaluate the text values and pass the references into COUNTIF as therangeargument. For reasons that are somewhat mysterious, COUNTIF will accept the result from INDIRECT without complaint....

Variances are listed in D5:D15, which is also thenamed rangevariance. The first formula in F5 simply sums all variances with theSUM function. This means it will work in any version of Excel without special handling. SeeWhy SUMPRODUCTfor more information. However, you’re free to calculate the variances directly in anarray operationif needed with the same results. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments....