SUMPRODUCT with multiple AND and OR criteria including partial texts

Joined
May 28, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Welcome to the MrExcel board!

I would recommend not using whole column references for a calculation like this. The calculation burden would be high. I suggest just choosing a range that will be sure to be big enough for your data. I have guessed 5000 in my formula below.

Try
Excel Formula:
=LET(e,E3:E5000,SUM(FILTER(B3:B5000,(A3:A5000>=D2)*(A3:A5000<E2)*(ISNUMBER(FIND(LEFT(F3:F5000,1),45)))*(ISNUMBER(SEARCH("B110500018",e))+ISNUMBER(SEARCH("B410500018",e))+ISNUMBER(SEARCH("B110500024",e))+ISNUMBER(SEARCH("B410500024",e))+ISNUMBER(SEARCH("B110500002",e))+ISNUMBER(SEARCH("B410500002",e))+ISNUMBER(SEARCH("B110500020",e))+ISNUMBER(SEARCH("B410500020",e))+ISNUMBER(SEARCH("B410900002",e))),0)))

Note also that I deleted your similar question from an old thread as we do not want duplicate questions. See Forum Rules (#12)
 
Upvote 0
Welcome to the MrExcel board!

I would recommend not using whole column references for a calculation like this. The calculation burden would be high. I suggest just choosing a range that will be sure to be big enough for your data. I have guessed 5000 in my formula below.

Try
Excel Formula:
=LET(e,E3:E5000,SUM(FILTER(B3:B5000,(A3:A5000>=D2)*(A3:A5000<E2)*(ISNUMBER(FIND(LEFT(F3:F5000,1),45)))*(ISNUMBER(SEARCH("B110500018",e))+ISNUMBER(SEARCH("B410500018",e))+ISNUMBER(SEARCH("B110500024",e))+ISNUMBER(SEARCH("B410500024",e))+ISNUMBER(SEARCH("B110500002",e))+ISNUMBER(SEARCH("B410500002",e))+ISNUMBER(SEARCH("B110500020",e))+ISNUMBER(SEARCH("B410500020",e))+ISNUMBER(SEARCH("B410900002",e))),0)))

Note also that I deleted your similar question from an old thread as we do not want duplicate questions. See Forum Rules (#12)
Thanks for the help but it is still not working.

I think the ISNUMBER(SEARCH) function might be the problem here. When I evaluate only the search function it returns correctly a bunch of #value which is correct. However, when we add the isnumber after that it only returns zeros, because the values are texts and not numbers.

How can we work around that?

thank you
 
Upvote 0
Olá Guilherme

A test with a small data sample
Observe column I that contains the texts to search - to make the formula easier to edit and more flexible
and
column G - a helper column to simplify the formula in J2

Pasta1
ABCDEFGHIJ
1StartEndSearchResult
201/01/202201/02/2022B11050001844
3DatesValuesTextCodesHelperB410500018
403/04/202210kuy11110B110500024
503/04/202211B41050002411111B410500024
601/01/202212kuy11110B110500002
731/01/202213blu B41050001845671B410500002
801/02/202214blu B110500002 bla51111B110500020
903/04/202115kuy11110B110500020
1003/01/202216blu B41050001811111B410500020
1127/01/202217B110500020 B11050001856781B410900002
1203/04/202118kuy11110
1303/04/202119kuy11110
14
Plan4
Cell Formulas
RangeFormula
J2J2=SUMPRODUCT(--(A4:A13>=D2),--(A4:A13<=E2),G4:G13,--((LEFT(F4:F13)="4")+(LEFT(F4:F13)="5")),B4:B13)
G4:G13G4=--(SUMPRODUCT(--ISNUMBER(SEARCH(I$2:I$11,E4)))>0)


Hope this helps

M.
 
Upvote 0
I will try to explain more clearly my issue because I still can`t get it to work.

1653859761334.png


That is my data.

I need to SUM everything in column B that fits the following criteria:

1. COLUMN A - 1-month data range (January)
2. COLUMN E - Has the following numbers in any of the cells in column B: B110500018; B410500018; B110500024; B410500024; B110500002; B410500002; B110500020; B410500020; B410900002
3. COLUMN F - Starts with 4 OR 5
4. COLUMN T - EXCLUDE from the sum the values that includes GS00 or AS00 or LV00
5. COLUMN D - INCLUDE the values that has GS00, AS00 or LV00 from column T only if they have NDI included in any cell in column D

Thanks a lot for the help guys and sorry in advance for my lack of ability with excel.
 
Upvote 0
Welcome to the MrExcel board!

I would recommend not using whole column references for a calculation like this. The calculation burden would be high. I suggest just choosing a range that will be sure to be big enough for your data. I have guessed 5000 in my formula below.

Try
Excel Formula:
=LET(e,E3:E5000,SUM(FILTER(B3:B5000,(A3:A5000>=D2)*(A3:A5000<E2)*(ISNUMBER(FIND(LEFT(F3:F5000,1),45)))*(ISNUMBER(SEARCH("B110500018",e))+ISNUMBER(SEARCH("B410500018",e))+ISNUMBER(SEARCH("B110500024",e))+ISNUMBER(SEARCH("B410500024",e))+ISNUMBER(SEARCH("B110500002",e))+ISNUMBER(SEARCH("B410500002",e))+ISNUMBER(SEARCH("B110500020",e))+ISNUMBER(SEARCH("B410500020",e))+ISNUMBER(SEARCH("B410900002",e))),0)))

Note also that I deleted your similar question from an old thread as we do not want duplicate questions. See Forum Rules (#12)
I have a feeling that something is not working with the ISNUMBER(SEARCH) function:

For example, when I filter January and B110500018 I get a count of 44 and the sum on column B adds a total of 265890.2 like the image below:

1653861304808.png


When I put in this formula that was supposed to do the same, I get 0:

=SUMPRODUCT($B:$B,($A:$A>=C2)*($A:$A<D2)*(--ISNUMBER(SEARCH($E:$E,"B110500018"))))

When I evaluate the: (--ISNUMBER(SEARCH($E:$E,"B110500018"))) It gives me only 12 ones and the rest is all zero like the image below:

1653861808805.png


It should be giving me 44 ones which is the number of cells that contain B110500018 in the text, right?
 
Upvote 0
=SUMPRODUCT($B:$B,($A:$A>=C2)*($A:$A<D2)*(--ISNUMBER(SEARCH($E:$E,"B110500018"))))

When I evaluate the: (--ISNUMBER(SEARCH($E:$E,"B110500018"))) It gives me only 12 ones and the rest is all zero like the image below:
You have the SEARCH arguments the wrong way around. That should be
SEARCH("B110500018",$E:$E)

I note that you are still using whole column references which I'm pretty sure will heavily burden the SUMPRODUCT function.

Here is my suggested formula working with a small set of dummy data that I made up. The green cells show the rows where the conditions are all met so the formula sums the two yellow cells. Do you think that is the correct calculation for what you are trying to do?

22 05 29.xlsm
ABCDEF
1
21/01/20141/02/201417
326/12/20214630
427/12/20213657
528/12/20216626
63/01/20147592
74/01/20144465
86/01/20148abc B410500020 def506
98/01/20142597
1017/01/20149349
1118/01/20141B410500024657
1220/01/20142505
1314/01/20149abcdef B410500018538
1426/01/20146625
1528/01/20149398
1630/01/20143608
172/02/20146541
183/02/20148565
196/02/20147414
207/02/20141344
218/02/20143374
Sheet5
Cell Formulas
RangeFormula
F2F2=LET(e,E3:E5000,SUM(FILTER(B3:B5000,(A3:A5000>=D2)*(A3:A5000<E2)*(ISNUMBER(FIND(LEFT(F3:F5000,1),45)))*(ISNUMBER(SEARCH("B110500018",e))+ISNUMBER(SEARCH("B410500018",e))+ISNUMBER(SEARCH("B110500024",e))+ISNUMBER(SEARCH("B410500024",e))+ISNUMBER(SEARCH("B110500002",e))+ISNUMBER(SEARCH("B410500002",e))+ISNUMBER(SEARCH("B110500020",e))+ISNUMBER(SEARCH("B410500020",e))+ISNUMBER(SEARCH("B410900002",e))),0)))


If it is still not working for you, then perhaps you could make up a small set (say 20-30 rows) of sample data and post that with XL2BB so the we can copy it to test with.
 
Upvote 0
Solution
You have the SEARCH arguments the wrong way around. That should be
SEARCH("B110500018",$E:$E)

I note that you are still using whole column references which I'm pretty sure will heavily burden the SUMPRODUCT function.

Here is my suggested formula working with a small set of dummy data that I made up. The green cells show the rows where the conditions are all met so the formula sums the two yellow cells. Do you think that is the correct calculation for what you are trying to do?

22 05 29.xlsm
ABCDEF
1
21/01/20141/02/201417
326/12/20214630
427/12/20213657
528/12/20216626
63/01/20147592
74/01/20144465
86/01/20148abc B410500020 def506
98/01/20142597
1017/01/20149349
1118/01/20141B410500024657
1220/01/20142505
1314/01/20149abcdef B410500018538
1426/01/20146625
1528/01/20149398
1630/01/20143608
172/02/20146541
183/02/20148565
196/02/20147414
207/02/20141344
218/02/20143374
Sheet5
Cell Formulas
RangeFormula
F2F2=LET(e,E3:E5000,SUM(FILTER(B3:B5000,(A3:A5000>=D2)*(A3:A5000<E2)*(ISNUMBER(FIND(LEFT(F3:F5000,1),45)))*(ISNUMBER(SEARCH("B110500018",e))+ISNUMBER(SEARCH("B410500018",e))+ISNUMBER(SEARCH("B110500024",e))+ISNUMBER(SEARCH("B410500024",e))+ISNUMBER(SEARCH("B110500002",e))+ISNUMBER(SEARCH("B410500002",e))+ISNUMBER(SEARCH("B110500020",e))+ISNUMBER(SEARCH("B410500020",e))+ISNUMBER(SEARCH("B410900002",e))),0)))


If it is still not working for you, then perhaps you could make up a small set (say 20-30 rows) of sample data and post that with XL2BB so the we can copy it to test with.
It works perfectly now. Thanks a lot Peter.

Would it make any difference to work with $E1:$E300000 instead of the whole column reference?

The reports that I extract as a data set often come with more the 200 thousand lines.

Best regards,
 
Upvote 0
It works perfectly now.
What formula did you end up using - SUMPRODUCT or LET/FILTER?
If SUMPRODUCT, can you post the full formula that you used?


Would it make any difference to work with $E1:$E300000 instead of the whole column reference?
Yes, that would save calculating more than 600,000 cells x the number of column references in the formula!
 
Upvote 0
That is the full formula:

=(LET(FF;'[Razão 30.05.xlsx]Exportação SAPUI5'!$F1:$F50000;EE;'[Razão 30.05.xlsx]Exportação SAPUI5'!$E1:$E50000;AA;'[Razão 30.05.xlsx]Exportação SAPUI5'!$A1:$A50000;BB;'[Razão 30.05.xlsx]Exportação SAPUI5'!$B1:$B50000;
SUMPRODUCT(BB;
(AA>=C2)*(AA<D2)*
((--ISNUMBER(FIND(4;LEFT(FF;1))))+(--ISNUMBER(FIND(5;LEFT(FF;1)))))*
((--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))))
))-
LET(FF;'[Razão 30.05.xlsx]Exportação SAPUI5'!$F1:$F50000;EE;'[Razão 30.05.xlsx]Exportação SAPUI5'!$E1:$E50000;AA;'[Razão 30.05.xlsx]Exportação SAPUI5'!$A1:$A50000;BB;'[Razão 30.05.xlsx]Exportação SAPUI5'!$B1:$B50000;TT;'[Razão 30.05.xlsx]Exportação SAPUI5'!$T1:$T50000;
SUMPRODUCT(BB;
(AA>=C2)*(AA<D2)*
((--ISNUMBER(FIND(4;LEFT(FF;1))))+(--ISNUMBER(FIND(5;LEFT(FF;1)))))*
((--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))))*
((--ISNUMBER(SEARCH("AS00";TT)))+(--ISNUMBER(SEARCH("LV00";TT)))+(--ISNUMBER(SEARCH("GS00";TT)))
))))+
LET(FF;'[Razão 30.05.xlsx]Exportação SAPUI5'!$F1:$F50000;EE;'[Razão 30.05.xlsx]Exportação SAPUI5'!$E1:$E50000;AA;'[Razão 30.05.xlsx]Exportação SAPUI5'!$A1:$A50000;BB;'[Razão 30.05.xlsx]Exportação SAPUI5'!$B1:$B50000;TT;'[Razão 30.05.xlsx]Exportação SAPUI5'!$T1:$T50000;DD;'[Razão 30.05.xlsx]Exportação SAPUI5'!$D1:$D50000;
SUMPRODUCT(BB;
(AA>=C2)*(AA<D2)*
((--ISNUMBER(FIND(4;LEFT(FF;1))))+(--ISNUMBER(FIND(5;LEFT(FF;1)))))*
((--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))))*
((--ISNUMBER(SEARCH("AS00";TT)))+(--ISNUMBER(SEARCH("LV00";TT)))+(--ISNUMBER(SEARCH("GS00";TT))))*
((--ISNUMBER(SEARCH("NDI";DD))))
))
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top