SUMPRODUCT with multiple AND and OR criteria including partial texts

[guilhermepaulinodias]

I have got an issue that I can't workout.

I need to perform a SUMPRODUCT with multiple AND and OR criterias. something like the following:

=SUMPRODUCT(--($A:$A>=D2)*(!$A:$A<E2)*($F:$F,{"4*";"5*"})(--ISNUMBER(SEARCH({"*B110500018*";"*B410500018*";"*B110500024*";"*B410500024*";"*B110500002*";"*B410500002*";"*B110500020*";"*B410500020*";"*B410900002*"},$E:$E))),$B:$B)

Basically what I need is the SUM of the values in column B that fits the following criterias:
1. The first 2 brackets refer to a date limit where D2= 01/01/2022 and E2=01/02/2022
2. The following bracket means that I only want the numbers that starts with 4 or 5.
3. The last one means I only want the cells that has ANY of the text strings in it.

The problem when I evaluate the formula is that the ISNUMBER(SEARCH) is giving me only zeros where I know for a fact that the column has 632 cells that contain one of the text strings mentioned. Can someone please help me with that?

Thanks

 

[Peter_SSs]

That is the full formula:

Wow, that is a monster!!!

A few comments about it.

• There is no need to keep repeating the LET sections. That only has to be done once in the formula.
• There is a shorter way to define AA, BB, DD etc without repeating the long file name & sheet name every time.
• There are more things that repeat in your formula. That means that you can introduce more 'names' into the LET function making it easier & shorter to refer to those repeating parts.
• You don't need to coerce ISNUMBER() from a True/False to a 1/0 with the leading "--" if you are doing other arithmetic operations with them as those other operations already do the coercion for you.
• The check for column F starting with 4 or 5 can be done with a single SEARCH (actually FIND will do since numbers do not have upper/lower case) as I showed in my post #7 formula
  ISNUMBER(FIND(LEFT(F3:F5000,1),45))

Combining all of the points above, I believe that this much shorter formula should do the same thing as your very long one. (There may be more shortenings but my brain got tired of dealing with such a long formula. )

=LET(r,'[Razão 30.05.xlsx]Exportação SAPUI5'!$A1:$T50000,AA,INDEX(r,0,1),BB,INDEX(r,0,2),DD,INDEX(r,0,4),EE,INDEX(r,0,5),FF,INDEX(r,0,6),TT,INDEX(r,0,20),EEE,ISNUMBER(SEARCH("B110500018",EE))+ISNUMBER(SEARCH("B410500018",EE))+ISNUMBER(SEARCH("B110500024",EE))+ISNUMBER(SEARCH("B410500024",EE))+ISNUMBER(SEARCH("B110500002",EE))+ISNUMBER(SEARCH("B410500002",EE))+ISNUMBER(SEARCH("B110500020",EE))+ISNUMBER(SEARCH("B410500020",EE))+ISNUMBER(SEARCH("B410900002",EE)),TTT,ISNUMBER(SEARCH("AS00",TT))+ISNUMBER(SEARCH("LV00",TT))+ISNUMBER(SEARCH("GS00",TT)),dte,(AA>=C2)*(AA<D2),start45,ISNUMBER(FIND(LEFT(FF,1),45)),
SUMPRODUCT(BB,dte*start45*EEE)
-SUMPRODUCT(BB,dte*start45*EEE*TTT)
+SUMPRODUCT(BB,dte*start45*EEE*TTT*ISNUMBER(SEARCH("NDI",DD))))