SUMPRODUCT using next IF and SEARCH function

[KP_SoCal]

The formula below works perfectly by itself...

=SUMPRODUCT(
--(AllocatedVeh!$A$2:$A$65000=
IF(Manifest!C8 = “TEXTILE”,AllocatedVeh!$A$2:$A$65000,Manifest!C8),
--(AllocatedVeh!$C$2:$C$65000=Manifest!D8),
--(AllocatedVeh!$F$2:$F$65000=Manifest!G8)
,AllocatedVeh!$I$2:$I$65000)


However, I need to incorporate the SEARCH function into this formula. It works successfully when I'm NOT using the nested IF statement (see underlined above). The formula with the SEARCH function and WITHOUT the nested IF statement would look like this...

=SUMPRODUCT(
--(ISNUMBER(SEARCH(AllocatedVeh!$A$2:$A$65000,Manifest!C8))),
--(AllocatedVeh!$C$2:$C$65000=Manifest!D8),
--(AllocatedVeh!$F$2:$F$65000=Manifest!G8)
,AllocatedVeh!$I$2:$I$65000)

So my question is, how do I apply the logic from the formula directly above containing SEARCH function to the first formula I have listed containing the nested IF statement. Thanks so much for any suggestions on this!

 

[Aladin Akyurek]

Are you perhaps intending?...

=SUMPRODUCT(
   --ISNUMBER(SEARCH(IF(Manifest!C8 = “TEXTILE”,"",Manifest!C8),
        AllocatedVeh!$A$2:$A$65000)),
   --(AllocatedVeh!$C$2:$C$65000=Manifest!D8),
   --(AllocatedVeh!$F$2:$F$65000=Manifest!G8),
   AllocatedVeh!$I$2:$I$65000)

 

[KP_SoCal]

Hi Aladin, this formula works only when Manifest!C8 field has one value to search. For instance, if Manifest!C8 = "PAPER" then the formula will work and sum the correct values. If Manifest!C8 = "PAPER, PENS, PENCILS" then the summed value doesn't return any results leaving the field blank.

I have tried all sorts of different combinations and can't seem to get it to work. Any other suggestions???

 

[Aladin Akyurek]

Hi Aladin, this formula works only when Manifest!C8 field has one value to search. For instance, if Manifest!C8 = "PAPER" then the formula will work and sum the correct values. If Manifest!C8 = "PAPER, PENS, PENCILS" then the summed value doesn't return any results leaving the field blank.

I have tried all sorts of different combinations and can't seem to get it to work. Any other suggestions???

Looks like...

=SUMPRODUCT(
   --ISNUMBER(SEARCH(AllocatedVeh!$A$2:$A$65000,
     IF(Manifest!C8 = “TEXTILE”,AllocatedVeh!$A$2:$A$65000,Manifest!C8))),
   --(AllocatedVeh!$C$2:$C$65000=Manifest!D8),
   --(AllocatedVeh!$F$2:$F$65000=Manifest!G8),
   AllocatedVeh!$I$2:$I$65000)

 

[KP_SoCal]

Goodness, this had me stumped for hours. Aladin, your solution worked perfectly!!! Thanks so much!!!

I have one final question (hopefully) pertaining to this formula. So far this formula is evaluating text fields to determine what is finally summed in AllocatedVeh!$I$2:$I$65000. I need to nest another if statement evaluating numberic values that are greater than a specified value.

Without using the "greater than" operator, the formula would look something like this:

=SUMPRODUCT(
--ISNUMBER(SEARCH(AllocatedVeh!$A$2:$A$65000,IF(Manifest!C8 = "TEXTILE",AllocatedVeh!$A$2:$A$65000,Manifest!C8))),
--(AllocatedVeh!$C$2:$C$65000=IF(Manifest!D8 = "TEXTILE",AllocatedVeh!$C$2:$C$65000, Manifest!D8)),
--(AllocatedVeh!$F$2:$F$65000=Manifest!G8),
AllocatedVeh!$I$2:$I$65000)

The formula works perfectly written in this context, but if I have a value of 12900 in Manifest!D8, I want to look at all values that are > 12900. For now, the formula only returns results for when values that are = 12900.

I tried integrating the greater than operator to no avail. Also notice that I do not need to make use of the search function for this particular criteria. Thanks again so much for any help you can provide for this final missing piece to my formula.

 

[Aladin Akyurek]

Goodness, this had me stumped for hours. Aladin, your solution worked perfectly!!! Thanks so much!!!

You are welcome.

I have one final question (hopefully) pertaining to this formula. So far this formula is evaluating text fields to determine what is finally summed in AllocatedVeh!$I$2:$I$65000. I need to nest another if statement evaluating numberic values that are greater than a specified value.

Without using the "greater than" operator, the formula would look something like this:

=SUMPRODUCT(
--ISNUMBER(SEARCH(AllocatedVeh!$A$2:$A$65000,IF(Manifest!C8 = "TEXTILE",AllocatedVeh!$A$2:$A$65000,Manifest!C8))),
--(AllocatedVeh!$C$2:$C$65000=IF(Manifest!D8 = "TEXTILE",AllocatedVeh!$C$2:$C$65000, Manifest!D8)),
--(AllocatedVeh!$F$2:$F$65000=Manifest!G8),
AllocatedVeh!$I$2:$I$65000)

The formula works perfectly written in this context, but if I have a value of 12900 in Manifest!D8, I want to look at all values that are > 12900. For now, the formula only returns results for when values that are = 12900.

I tried integrating the greater than operator to no avail. Also notice that I do not need to make use of the search function for this particular criteria. Thanks again so much for any help you can provide for this final missing piece to my formula.

Up to now, we had to do with an equality test, which works well with an IF expression in the condition part...

The following is an attempt to do it with the GREATER THAN test as you ask for...

=SUMPRODUCT(
 --ISNUMBER(SEARCH(AllocatedVeh!$A$2:$A$65000,
   IF(Manifest!C8 = "TEXTILE",AllocatedVeh!$A$2:$A$65000,Manifest!C8))),
 --(AllocatedVeh!$C$2:$C$65000 >
   IF(Manifest!D8 = "TEXTILE",N(AllocatedVeh!$C$2:$C$65000)-1, Manifest!D8)),
 --(AllocatedVeh!$F$2:$F$65000=Manifest!G8),
 AllocatedVeh!$I$2:$I$65000)

 

[KP_SoCal]

Aladin, extend your hand and give yourself a nice firm handshake from me! Thank you, thank you, thank you again so much for your help on this!!!

If you lived in Southern California, I would invite you over for our family's Christmas dinner. Have a great Thanksgiving!