Let's say that A2 contains the following text value...

Code:

`Some text (), some other text ()`

LEN(A2) returns 32, which represents the number of characters within the text string.

SUBSTITUTE(A2,"()","") returns "Some text , some other text ", which represents the text value that remains after each instance of "()" has been removed.

LEN(SUBSTITUTE(A2,"()","")) returns 28, which represents the number of characters that remain after each instance of "()" has been removed.

LEN(A2)-LEN(SUBSTITUTE(A2,"()","")) returns 4, which represents the number of characters removed. In effect, it tells us the number of times "(" and ")" occurs.

The number 4 is then divided by 2, since there are two characters in "()", and returns 2, which represents the number of times "()" occurs.

In the SUMPRODUCT formula, this evaluation process takes place for each cell in the given range. To see how the formula is evaluated by Excel, select the cell containing the formula, and then select...

Formula > Evaluate Formula

...and click on 'Evaluate'.