Weighted average with sumproduct and multiple conditions

[raw_enha]

Hi, I made a formula that works: =SUMPRODUCT(Answer!U2:U65535,Answer!AD2:AD65535)/SUM(Answer!AD2:AD65535)
however, I need to update it for multiple conditions and I'm having some trouble with the specifics.

Referencing sheet "Answer", I want to perform the same calculation as above but for the sum of the following conditions:

Corresponding value in column A contains text "cr_3" or contains "dr_3" or contains "dv_3".

Those text values are partial matches (because, for instance, I don't want results from "cr_2" and should not be case sensitive.

I've been trying this between other projects for a few days now, but I'm just not getting anywhere. Any assistance would be greatly appreciated.

Also posted on Ozgrid, but no answer so far: http://www.ozgrid.com/forum/showthread.php?t=165637

 

[BiocideJ]

Corresponding value in column A contains text "cr_3" or contains "dr_3" or contains "dv_3".

Do you want the denominator also limited by these criteria? i.e. anything with these values should be excluded altogether? Or should that criteria only limit the numerator?

For lack of more direction, here is an initial stab at it affecting only the numerator side of the equation:

=SUMPRODUCT(Answer!U2:U65535,Answer!AD2:AD65535,--(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(Answer!A2:A65535),"dv_3",""),"dr_3",""),"cr_3","")) < LEN(Answer!A2:A65535))) / SUMPRODUCT(Answer!AD2:AD65535)

 

[Aladin Akyurek]

Hi, I made a formula that works: =SUMPRODUCT(Answer!U2:U65535,Answer!AD2:AD65535)/SUM(Answer!AD2:AD65535)
however, I need to update it for multiple conditions and I'm having some trouble with the specifics.

Referencing sheet "Answer", I want to perform the same calculation as above but for the sum of the following conditions:

Corresponding value in column A contains text "cr_3" or contains "dr_3" or contains "dv_3".

Those text values are partial matches (because, for instance, I don't want results from "cr_2" and should not be case sensitive.

I've been trying this between other projects for a few days now, but I'm just not getting anywhere. Any assistance would be greatly appreciated.

Also posted on Ozgrid, but no answer so far: http://www.ozgrid.com/forum/showthread.php?t=165637

Does this...

=SUMPRODUCT(
  MMULT(ISNUMBER(SEARCH({"cr_3","dr_3","dv_3"},Answer!A2:A65535))+0,{1;1;1}),
    Answer!U2:U65535,
    Answer!AD2:AD65535)/
   SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

yield the intended calculation?

 

[Aladin Akyurek]

Does this...

=SUMPRODUCT(
MMULT(ISNUMBER(SEARCH({"cr_3","dr_3","dv_3"},Answer!A2:A65535))+0,{1;1;1}),
 Answer!U2:U65535,
 Answer!AD2:AD65535)/
SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

yield the intended calculation?

Part of the formula did not make it to the board... It should be:

=SUMPRODUCT(
  MMULT(ISNUMBER(SEARCH({"cr_3","dr_3","dv_3"},Answer!A2:A65535))+0,{1;1;1})>0)+0,
  Answer!U2:U65535,
  Answer!AD2:AD65535)/
 SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

 

[raw_enha]

Aladin, thank you for your response. Unfortunately, it just returns the message "The formula you have typed contains an error."

I tried breaking the formula into pieces in notepad to see see if I can find the issue, but no luck so far. Using Excel 2007.

 

[Aladin Akyurek]

Aladin, thank you for your response. Unfortunately, it just returns the message "The formula you have typed contains an error."

I tried breaking the formula into pieces in notepad to see see if I can find the issue, but no luck so far. Using Excel 2007.

A missing paren is the culprit...

=SUMPRODUCT(
  (MMULT(ISNUMBER(SEARCH({"cr_3","dr_3","dv_3"},Answer!A2:A65535))+0,{1;1;1})>0)+0, 
  Answer!U2:U65535, Answer!AD2:AD65535)/ 
  SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

 

[raw_enha]

Hi Aladin, using the correction gives the same error message, but this time excel auto-highlights what it seems to think is a problem:

SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

That's from the end of the formula, excel highlights the first quotation mark in that line as if it doesn't like it. Excel does recognize that it falls within the criteria parameter of the sumif function.

Let me know if you have any ideas, thank you.

 

[Aladin Akyurek]

Hi Aladin, using the correction gives the same error message, but this time excel auto-highlights what it seems to think is a problem:

SUM(SUMIF(Answer!A2:A65535,"*"{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

That's from the end of the formula, excel highlights the first quotation mark in that line as if it doesn't like it. Excel does recognize that it falls within the criteria parameter of the sumif function.

Let me know if you have any ideas, thank you.

Missing an &...

=SUMPRODUCT(
  (MMULT(ISNUMBER(SEARCH({"cr_3","dr_3","dv_3"},Answer!A2:A65535))+0,{1;1;1})>0)+0, 
  Answer!U2:U65535, Answer!AD2:AD65535)/ 
 SUM(SUMIF(Answer!A2:A65535,"*"&{"cr_3","dr_3","dv_3"}&"*",AD2:AD65535))

 

[raw_enha]

Thanks Aladin! You're a genius at this.
There was actually 1 more error, but I corrected it when I saw DIV/) issue. The last parameter:

AD2:AD65535

was supposed to be:

Answer!AD2:AD65535

THANK YOU SO MUCH!

 

[Aladin Akyurek]

Thanks Aladin! You're a genius at this.
There was actually 1 more error, but I corrected it when I saw DIV/) issue. The last parameter:

AD2:AD65535

was supposed to be:

Answer!AD2:AD65535

That too...

THANK YOU SO MUCH!

You are welcome. And thanks for the kind feedback.