SUMPRODUCT with multiple AND and OR criteria including partial texts

guilhermepaulinodias · May 28, 2022

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 · May 28, 2022

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)

guilhermepaulinodias · May 29, 2022

Peter_SSs said:
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

Marcelo Branco · May 29, 2022

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

A

B

C

D

E

F

G

H

I

J

1

Start

End

Search

Result

2

01/01/2022

01/02/2022

B110500018

44

3

Dates

Values

Text

Codes

Helper

B410500018

4

03/04/2022

10

kuy

1111

0

B110500024

5

03/04/2022

11

B410500024

1111

1

B410500024

6

01/01/2022

12

kuy

1111

0

B110500002

7

31/01/2022

13

blu B410500018

4567

1

B410500002

8

01/02/2022

14

blu B110500002 bla

5111

1

B110500020

9

03/04/2021

15

kuy

1111

0

B110500020

10

03/01/2022

16

blu B410500018

1111

1

B410500020

11

27/01/2022

17

B110500020 B110500018

5678

1

B410900002

12

03/04/2021

18

kuy

1111

0

13

03/04/2021

19

kuy

1111

0

14

Plan4

Cell Formulas
Range	Formula
J2	J2	=SUMPRODUCT(--(A4:A13>=D2),--(A4:A13<=E2),G4:G13,--((LEFT(F4:F13)="4")+(LEFT(F4:F13)="5")),B4:B13)
G4:G13	G4	=--(SUMPRODUCT(--ISNUMBER(SEARCH(I$2:I$11,E4)))>0)

Hope this helps

M.

guilhermepaulinodias · May 29, 2022

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

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.

guilhermepaulinodias · May 29, 2022

Peter_SSs said:
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:

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:

It should be giving me 44 ones which is the number of cells that contain B110500018 in the text, right?

Peter_SSs · May 29, 2022

guilhermepaulinodias said:
=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

A

B

C

D

E

F

1

2

1/01/2014

1/02/2014

17

3

26/12/2021

4

630

4

27/12/2021

3

657

5

28/12/2021

6

626

6

3/01/2014

7

592

7

4/01/2014

4

465

8

6/01/2014

8

abc B410500020 def

506

9

8/01/2014

2

597

10

17/01/2014

9

349

11

18/01/2014

1

B410500024

657

12

20/01/2014

2

505

13

14/01/2014

9

abcdef B410500018

538

14

26/01/2014

6

625

15

28/01/2014

9

398

16

30/01/2014

3

608

17

2/02/2014

6

541

18

3/02/2014

8

565

19

6/02/2014

7

414

20

7/02/2014

1

344

21

8/02/2014

3

374

Sheet5

Cell Formulas
Range	Formula
F2	F2	=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.

guilhermepaulinodias · May 29, 2022

Peter_SSs said:
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
A B C D E F
1
2 1/01/2014 1/02/2014 17
3 26/12/2021 4 630
4 27/12/2021 3 657
5 28/12/2021 6 626
6 3/01/2014 7 592
7 4/01/2014 4 465
8 6/01/2014 8 abc B410500020 def 506
9 8/01/2014 2 597
10 17/01/2014 9 349
11 18/01/2014 1 B410500024 657
12 20/01/2014 2 505
13 14/01/2014 9 abcdef B410500018 538
14 26/01/2014 6 625
15 28/01/2014 9 398
16 30/01/2014 3 608
17 2/02/2014 6 541
18 3/02/2014 8 565
19 6/02/2014 7 414
20 7/02/2014 1 344
21 8/02/2014 3 374
Sheet5
Cell Formulas
Range Formula
F2 F2 =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,

Peter_SSs · May 29, 2022

guilhermepaulinodias said:
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?

guilhermepaulinodias said:
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!

guilhermepaulinodias · May 30, 2022

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))))
))

SUMPRODUCT with multiple AND and OR criteria including partial texts

guilhermepaulinodias

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

guilhermepaulinodias

New Member

Marcelo Branco

MrExcel MVP

guilhermepaulinodias

New Member

guilhermepaulinodias

New Member

Peter_SSs

MrExcel MVP, Moderator

guilhermepaulinodias

New Member

Peter_SSs

MrExcel MVP, Moderator

guilhermepaulinodias

New Member

Similar threads

Forum statistics

Share this page

SUMPRODUCT with multiple AND and OR criteria including partial texts

New Member

Excel Facts

MrExcel MVP, Moderator

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock