sumproduct Rows and Column reference that Contains string

[djossh]

Please help. im trying to sumtotal all column(s) that contains text string "Meal" in col B7:O7 (Book2/sheet1) and (Book2/sheet1 Col A8:A37 that match with my row refence in Col A7 (Book1/sheet1).... sum range is B8:O37



Here is my current formula... and its not working.. i dont know how to write the right formula that will give me the results i wanted.. thanks

(In Book1/sheet1 cell E7)

=SUMPRODUCT(('[Book2.xls]sheet1'!$B$7:$O$7=SUMPRODUCT(ISNUMBER(SEARCH("*"&Meal&"*",'[Book2.xls]sheet1'!$B$7:$O$7)*('[Book2.xls]Sheet1'!$A$8:$A$37=$A7)*('[Book2.xls]sheet1'!$B$8:$O$37)))))

 

[Aladin Akyurek]

Please help. im trying to sumtotal all column(s) that contains text string "Meal" in col B7:O7 (Book2/sheet1) and (Book2/sheet1 Col A8:A37 that match with my row refence in Col A7 (Book1/sheet1).... sum range is B8:O37



Here is my current formula... and its not working.. i dont know how to write the right formula that will give me the results i wanted.. thanks

(In Book1/sheet1 cell E7)

=SUMPRODUCT(('[Book2.xls]sheet1'!$B$7:$O$7=SUMPRODUCT(ISNUMBER(SEARCH("*"&Meal&"*",'[Book2.xls]sheet1'!$B$7:$O$7)*('[Book2.xls]Sheet1'!$A$8:$A$37=$A7)*('[Book2.xls]sheet1'!$B$8:$O$37)))))

Control+shift+enter, not just enter:

=SUM(
   IF(ISNUMBER(SEARCH("Meal",'[Book2.xls]sheet1'!$B$7:$O$7)),
   IF('[Book2.xls]Sheet1'!$A$8:$A$37=$A7,
     '[Book2.xls]sheet1'!$B$8:$O$37)))

 

[djossh]

Control+shift+enter, not just enter:

=SUM(
   IF(ISNUMBER(SEARCH("Meal",'[Book2.xls]sheet1'!$B$7:$O$7)),
   IF('[Book2.xls]Sheet1'!$A$8:$A$37=$A7,
     '[Book2.xls]sheet1'!$B$8:$O$37)))

Thanks for your quick reply bu it's not working the way it should be..

 

[Aladin Akyurek]

Thanks for your quick reply bu it's not working the way it should be..

What did you get as result - an incorrect value, an error?

 

[djossh]

What did you get as result - an incorrect value, an error?

it gives me "0" (zero) results...i think its not summing up the sumrange?? (Im not sure, i really dont know what's causes "0" result)

 

[Aladin Akyurek]

it gives me "0" (zero) results...i think its not summing up the sumrange?? (Im not sure, i really dont know what's causes "0" result)

Did you apply control+shift+enter to the formula? That is: Press the control and shift keys down while you hit the enter key...