SUMPRODUCT using next IF and SEARCH function

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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! :)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Are you perhaps intending?...

Code:
=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

Board Regular
Joined
Nov 17, 2009
Messages
116
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...
Code:
=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

Board Regular
Joined
Nov 17, 2009
Messages
116

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...

Rich (BB code):
=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

Board Regular
Joined
Nov 17, 2009
Messages
116
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!!! :p :p :p

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

Watch MrExcel Video

Forum statistics

Threads
1,122,729
Messages
5,597,791
Members
414,176
Latest member
LK88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top