# SUMPRODUCT mistery

#### pupcis

##### New Member
Hey, I'm trying to sum different fractional values of elements from a column with a 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:

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You need to include the division by the LEN of column C within the array. And it's not advisable to refer to entire columns. Something like this entered with ctrl+shift+enter (change the ranges as required)...

=SUM(IF(A2:A4>0,A2:A4*(LEN(C2:C4)-LEN(SUBSTITUTE(C2:C4,"f","")))/LEN(C2:C4)))

Hello pupcis,

It works for me ........but the formula needs to be "array entered", i.e. confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula - did you do that?

Otherwise what result do you get? I presume you are using Excel 2007 or later, otherwise you can't use whole column references like that

Last edited:
=SUM(IF(A2:A4>0,A2:A4*(LEN(C2:C4)-LEN(SUBSTITUTE(C2:C4,"f","")))/LEN(C2:C4)))

You could also simplify that further, Neil and eliminate another LEN....

=SUM(IF(A2:A4>0,A2:A4*(1-LEN(SUBSTITUTE(C2:C4,"f",""))/LEN(C2:C4))))

You could also simplify that further, Neil and eliminate another LEN....

=SUM(IF(A2:A4>0,A2:A4*(1-LEN(SUBSTITUTE(C2:C4,"f",""))/LEN(C2:C4))))

Barry,

I can see that your formula works, but logic tells me it shouldn't! I can't understand how this:
PHP:
``LEN(C2:C4)-LEN(SUBSTITUTE(C2:C4,"f",""))``
is equal to this:
PHP:
``1-LEN(SUBSTITUTE(C2:C4,"f",""))``

One returns {2;2;1}, the other returns {-2;-2;-2}

....but those two parts aren't the same

This part

(LEN(C2:C4)-LEN(SUBSTITUTE(C2:C4,"f","")))/LEN(C2:C4)

Can be reduced to

1-LEN(SUBSTITUTE(C2:C4,"f",""))/LEN(C2:C4)

It's the equivalent of changing

(B2-A2)/B2

to

1-A2/B2

Got it! Thanks for enlightening me!!

Hello pupcis,

It works for me ........but the formula needs to be "array entered", i.e. confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula - did you do that?

Otherwise what result do you get? I presume you are using Excel 2007 or later, otherwise you can't use whole column references like that
Thanks a lot barry houdini !
I was missing the CTRL + SHIFT + Enter array formula concept.

Replies
15
Views
374
Replies
7
Views
292
Replies
3
Views
321
Replies
10
Views
648
Replies
5
Views
271

1,202,990
Messages
6,052,954
Members
444,621
Latest member
MIKOLAJ_R

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back