column contains strings of list in sumproduct

[mos]

Hello,

basically I want to do the following:

1. summing up the value of each category of "product" for example
2. only of rows where "comment" contains one of the words in the list

I found this code, but I cant find a way to implement it in my sumproduct.
As soon as I dont use the Sumproduct in what I found, it doesnt work anymore.

= SUMPRODUCT(ISNUMBER(1/(SEARCH($D$3:$D$6;0&C9;1)>1))*1))

whole code (I try to use) C9 --- C9:C12

 =SUMPRODUCT(($A$9:$A$12=A4)*($B$9:$B$12)* SUMPRODUCT(ISNUMBER(1/(SEARCH($D$3:$D$6;0&C9:C12;1)>1))*1))

the code above is in the row with the blue 1,0,1,1.
what das the "0&" do to the link to C9?

Does anybody have an hint how to modify or what else to use?

Other solutions give always back TRUE, if my list contains empty cells..


Thanks, cheers,
mos

 

[Aladin Akyurek]

Hello,

basically I want to do the following:

1. summing up the value of each category of "product" for example
2. only of rows where "comment" contains one of the words in the list

I found this code, but I cant find a way to implement it in my sumproduct.
As soon as I dont use the Sumproduct in what I found, it doesnt work anymore.

= SUMPRODUCT(ISNUMBER(1/(SEARCH($D$3:$D$6;0&C9;1)>1))*1))

whole code (I try to use) C9 --- C9:C12

 =SUMPRODUCT(($A$9:$A$12=A4)*($B$9:$B$12)* SUMPRODUCT(ISNUMBER(1/(SEARCH($D$3:$D$6;0&C9:C12;1)>1))*1))

the code above is in the row with the blue 1,0,1,1.
what das the "0&" do to the link to C9?

Does anybody have an hint how to modify or what else to use?

Other solutions give always back TRUE, if my list contains empty cells..


Thanks, cheers,
mos

B4, control+shift+enter (CSE), not just enter, and copy down:

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH(" "&TRANSPOSE($D$3:$D$4)&" ",
         " "&$C$9:$C$12&" ")),ROW($D$3:$D$4)^0)),
    --($A$9:$A$12=A4),
    $B$9:$B$12)

Note. CSE is required because of a TRANSPOSE call.

 

[Marcelo Branco]

B4, control+shift+enter (CSE), not just enter, and copy down:

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH(" "&TRANSPOSE($D$3:$D$4)&" ",
         " "&$C$9:$C$12&" ")),ROW($D$3:$D$4)^0)),
    --($A$9:$A$12=A4),
    $B$9:$B$12)

Note. CSE is required because of a TRANSPOSE call.

Aladin, very beautiful!!!! ray:

I was breaking my head and got a solution only with a helper column...

One more for my collection!

M.

 

[mos]

It is actually awesome. Amazing how matrixmult can help you in real life.

Great that some are so passionate about it

Thanks a lot.

 

[Aladin Akyurek]

Aladin, very beautiful!!!! ray:

I was breaking my head and got a solution only with a helper column...

One more for my collection!

M.

It is actually awesome. Amazing how matrixmult can help you in real life.

Great that some are so passionate about it

Thanks a lot.

You are welcome. Thanks for providing feedback.