Sumproduct returning value error or blank?

[brettthomas]

Hello,

I am trying to do what I thought would be extremely simple: Find text in column D, and for each "true", sum it's respective values in column E. In other words, I'm doing a simple =SUMIF(D2:D44,L10,E2:E44), but instead of matching the exact contents of L10, I need it to find the contents of L10 WITHIN column D (ie: find "I am tearing" in "I am tearing my hair out"). I have no idea why the following won't work. It gives a value error if entered normally, or evaluates incorrectly to "0" if entered as an array formula:

=SUMPRODUCT(IF(ISNUMBER(SEARCH(L10,D2:D44)),1,0),E2:E44)

Same thing here:

=SUMPRODUCT(--(ISNUMBER(SEARCH(L10,D2:D44))),E2:E44)

{=IF(ISNUMBER(SEARCH(L10,D2:D65)),1,0)} evaluates correctly to an array {0,0,0,1,0,0,1,1,0,1,0,0,etc}, and E2:E44 is an array, so why can't Sumproduct multiply and sum these arrays? There are currently no blank cells in either column, but it will eventually need to be able to handle (meaning ignore) blank cells. Any ideas???

Thanks in advance!

-Brett

 

[T. Valko]

Hello,

I am trying to do what I thought would be extremely simple: Find text in column D, and for each "true", sum it's respective values in column E. In other words, I'm doing a simple =SUMIF(D2:D44,L10,E2:E44), but instead of matching the exact contents of L10, I need it to find the contents of L10 WITHIN column D (ie: find "I am tearing" in "I am tearing my hair out"). I have no idea why the following won't work. It gives a value error if entered normally, or evaluates incorrectly to "0" if entered as an array formula:

=SUMPRODUCT(IF(ISNUMBER(SEARCH(L10,D2:D44)),1,0),E2:E44)

Same thing here:

=SUMPRODUCT(--(ISNUMBER(SEARCH(L10,D2:D44))),E2:E44)

{=IF(ISNUMBER(SEARCH(L10,D2:D65)),1,0)} evaluates correctly to an array {0,0,0,1,0,0,1,1,0,1,0,0,etc}, and E2:E44 is an array, so why can't Sumproduct multiply and sum these arrays? There are currently no blank cells in either column, but it will eventually need to be able to handle (meaning ignore) blank cells. Any ideas???

Thanks in advance!

-Brett

You can use the SUMIF function.

=SUMIF(D2:D44,"*"&L10&"*",E2:E44)

Or, the SUMPRODUCT version:

=SUMPRODUCT(--(ISNUMBER(SEARCH(L10,D2:D44))),E2:E44)

 

[brettthomas]

Thanks so much for your help! The Sumproduct version that you gave me is actually the exact equation that I had in my original post, which was not working. I have realized, though, that it was in fact working, but the cell containing the equation will not update when something changes in D2:D44 or E2:E44, so it was stuck evaluating to "0" until I reopened the cell containing the equation, then hit enter again to lock it in after a change was made to one of those ranges. It then reevaluates to the correct number, but I have to do that every time a change is made. . . . not good.

I've never seen this before. Any idea why it won't automatically update when a change is made to cells/arrays within the equation? Strangely enough, your SUMIF version won't automatically update either. I have hundreds of instances of SUMIF in my spreadsheets, and it has always automatically updated until now. Very weird.

Thanks again!

-Brett

 

[brettthomas]

Ahhhh!!!! Disregard the last post. I just spent a day and a half pulling my hair out because "Calculate" had somehow been switched to "Manual". I've never even opened that tab in the preferences. Who knows why that happened, but at least it works now. Thanks again for your time and help!

 

[T. Valko]

Ahhhh!!!! Disregard the last post. I just spent a day and a half pulling my hair out because "Calculate" had somehow been switched to "Manual". I've never even opened that tab in the preferences. Who knows why that happened, but at least it works now. Thanks again for your time and help!

Hmmm...

For some reason I did not even see your other post until just now. I could have steered you in that direction if I had seen your post.

Glad you got it straightened out, though. Thanks for the feedback!