Dave Bruns

In this example, the goal is to count the number of cells in a range that contain negative numbers. For convenience, the range B5:B15 is named data. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods...Read more

In this example, the goal is to count the number of cells in a range that contain positive numbers. For convenience, the range B5:B15 is named data. This problem can be solved with the COUNTIF function or the SUMPRODUCT function....Read more

For each cell in the range, SUBSTITUTE removes all the o's from the text, then LEN calculates the length of the text without o's. This number is then subtracted from the length of the text with o's.

Because we are using SUMPRODUCT, the result of all this calculation is a list of items (...Read more

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.Read more

The LEN function is fully automatic. In the example, the formula in the active cell is:

=LEN(B5)

The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters, as you can see in...Read more

For each cell in the range, SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from the length of the text with spaces, and the number 1 is added to the final result, since the number of words is the number of...Read more

In this example, the goal is to count the total number of words in a cell. Excel doesn't have a dedicated function for counting words. However, with a little ingenuity, you can create a formula to perform this task using a combination of built-in functions. In newer versions of Excel, the best...Read more

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars. The LEFT function then extracts characters starting at the left side of the text, up to (position - 1...Read more