SUMPRODUCT mistery

[pupcis]

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!

 

[njimack]

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)))

 

[barry houdini]

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

 

[barry houdini]

=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))))

 

[njimack]

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:

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

is equal to this:

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

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

 

[barry houdini]

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

 

[njimack]

Got it! Thanks for enlightening me!!

 

[pupcis]

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.