Hey, I'm trying to sum different fractional values of elements from a column with a

A | B | C

-------------

1 | - | fcdef

2 | - | cdfef

1 | - | cdef

The fractions are defined as the number of times the caracter "f" appears on the corresponding line in the C column divided by the total number of characters.

For this example, the computation should go:

1 * 2/5 + 2 * 2/5 + 1 * 1/4 = 0.4 + 0.8 + 0.25 = 1.45

The formula I'm using is:

=SUMPRODUCT(A:A, (LEN(C:C)-LEN(SUBSTITUTE(C:C,"f","")))/IF(LEN(C:C)>0,LEN(C:C),1))

and the result is always the total number of occurrences of the "f" character in column C (7 in this case).

If I split it into smaller formulas, they work just fine.

If I generate the 2nd array from the sumproduct function in a column also behaves as expected, but I wouldn't like to create additional columns.

Can somebody tell me what am I doing wrong or what's the way to do it?

Thanks!

*sumproduct*formula. e.g.:A | B | C

-------------

1 | - | fcdef

2 | - | cdfef

1 | - | cdef

The fractions are defined as the number of times the caracter "f" appears on the corresponding line in the C column divided by the total number of characters.

For this example, the computation should go:

1 * 2/5 + 2 * 2/5 + 1 * 1/4 = 0.4 + 0.8 + 0.25 = 1.45

The formula I'm using is:

=SUMPRODUCT(A:A, (LEN(C:C)-LEN(SUBSTITUTE(C:C,"f","")))/IF(LEN(C:C)>0,LEN(C:C),1))

and the result is always the total number of occurrences of the "f" character in column C (7 in this case).

If I split it into smaller formulas, they work just fine.

If I generate the 2nd array from the sumproduct function in a column also behaves as expected, but I wouldn't like to create additional columns.

Can somebody tell me what am I doing wrong or what's the way to do it?

Thanks!

Last edited: