Explanation The LEN function is fully automatic. All characters are counted, including space characters, as you’re free to see in cell C9. Numbers Numbers in Excel (including dates, times, currency, etc.) are often formatted with anumber format. It’s important to understand that the characters that makeup numbers are counted in their raw form (i.e. number formatting is not included). LEN will also count characters in numbers, but number formatting is not included....

Explanation SUMPRODUCT accepts the range B3:B6 as an array of four cells. For each cell in the array, LEN calculates the length of the text as a number. The result is an array that contains 4 numbers. SUMPRODUCT then sums the items in this array and returns the total. LEN will also count characters in numbers, but number formatting is not included. SUMPRODUCT Function The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products....

This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below. COUNTIF function TheCOUNTIF functioncounts values in a range that meet supplied criteria. A positive number represents the count of a value inrange2that was found inrange1. A zero indicates a value that was not found. However, it must be entered as anarray formulawith control + shift + enter. In thecurrent version of Excel, it just works....

Excel doesn’t have a dedicated function for counting words. In newer versions of Excel, the best approach is to use the TEXTSPLIT and COUNTA functions. This is a great example of how newer functions in Excel are simplifying older more complex formulas. While the older SUBSTITUTE option does work, it works indirectly and is not very intuitive. The TEXTSPLIT option is logical and straightforward; it just makes sense. The result from TEXTSPLIT is an array that contains the six words in the title cell B5....

In other words, we have a list of word counts, with one word count per cell. SUMPRODUCT then sums this list and returns a total for all cells in the range. Note that the formula inside SUMPRODUCT will return 1 even if a cell is empty. The double hyphen coerces the result to 1’s and 0’s. We use TRIM again to ensure we don’t count cells that have one or more spaces....

In the example shown, each row in the table represents a stock trade. On some dates, more than one trade is performed. The goal is to count trading days the number of unique dates on which some kind of trade occurred. The 5 dates are 1-Oct-20, 5-Oct-20, 7-Oct-20, 9-Oct-20, and 12-Oct-20. Note: The COUNT function counts numeric values, while theCOUNTA functionwill count both numeric and text values. Depending on the situation, it may make sense to use one or the other....

dates that contain dates and times). In addition, we also want to create the table of results seen in E7:F9. For convenience,datais thenamed rangeB5:B16. TheINT functionsimply removes the decimal portion of the date, leaving the serial number intact. Next, theUNIQUE functionis used to remove duplicate dates. Instead, the dates exist in anarrayreturned by the INT function.COUNTIF requires a rangefor therangeargument. First, we use INT to strip times from dates and assign the result tod....

This formula shows one way to count unique values, as long as they are numeric. We use it here as a roundabout way to count unique numeric values. Note: you could also use SUMPRODUCT to sum the items in the array. The FREQUENCY formula, while more complicated, calculates much faster. These values can be numbers, cell references, ranges, arrays, and constants, in any combination. SUM can handle up to 255 individual arguments....

This formula shows one way to count unique values, as long as they are numeric. We use it here as a roundabout way to count unique numeric values. To apply criteria, we use the IF function. The FALSE values are automatically ignored. This is the feature that allows this approach to work. This is done with thedouble-negative(–). These formulas can be adapted toapply logical criteria. These values can be numbers, cell references, ranges, arrays, and constants, in any combination....

This array is fed into FREQUENCY as thedata_arrayargument. When a number has already been counted, FREQUENCY will return zero. This is a key feature in the operation of this formula. For more information,see this page. These formulas can be adapted toapply logical criteria. A pivot table is also an excellent way tolist and count unique values. MATCH supports approximate and exact matching, andwildcards(* ?) For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet....

When a number has already been counted, FREQUENCY will return zero. Note: I adapted the formulas above fromMike Girvin’sexcellent book on array formulas,Control-Shift-Enter. These formulas can be adapted toapply logical criteria. MATCH supports approximate and exact matching, andwildcards(* ?) For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula....

Explanation This example uses the UNIQUE function to extract unique values. Since there are seven items inarray, COUNTA returns 7. This formula is dynamic and will recalculate immediately when source data is changed. The formula in D5 is: Which returns the seven values seen in D5:D11. Then COUNTA counts the zero. This approach isexplained in more detail here. you’re able to also filterunique values with criteria. This alternative formula will count all values returned by UNIQUE that have a length greater than zero....

This array is configured as a divisor with 1 as the numerator. a value that appears 4 times in data will generate 4 values = 0.25). Finally, the SUMPRODUCT function sums all values in the array and returns the result. It works because using an empty string ("") for criteria will count empty cells. Slow Performance? For larger data sets, you may want to switch to a formula based on the FREQUENCY function....