# Formula to sum a text join based on multiple Criteria

#### Lukma

##### Board Regular
Hi friends

Happy Weekend to you all, i am having trouble with the formula let say my Week Number Listed in Column A, and in Column B i have name of Zone and in Column C, i have numbers of Change made by each zone,
Now i have use a Text Join to Join 2 weeks number in Column D, as criteria 1, while i have my criteria 2 in column E,
In column F, i need to sum how many change made by each zone for 2 weekNum based on my criteria

Please i hope am able to explain better and i hope i could be able to have a solution to this formula

Appreciate any support

WeekNumNameNo's of ChangeTextjoin for Two Weeks Criteria 1Criteria 2Sum Change Answer
1Zone-111&2Zone-1
1Zone-111&2Zone-2
2Zone-211&2Zone-3
2Zone-11
1Zone-31
3Zone-21
4Zone-11
2Zone-21
1Zone-31
2Zone-31

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### jasonb75

##### Well-known Member
Don't use textjoin, have 2 separate columns, 1 for each of the 2 week numbers. Then it's a simple sumifs formula.
Book1
ABCDEFGH
1WeekNumNameNo's of ChangeCriteria 1 1st weekCriteria 1 2nd weekCriteria 2Sum Change Answer
21Zone-1112Zone-13
31Zone-1112Zone-22
42Zone-2112Zone-33
52Zone-11
61Zone-31
73Zone-21
84Zone-11
92Zone-21
101Zone-31
112Zone-31
Sheet3
Cell Formulas
RangeFormula
G2:G4G2=SUM(SUMIFS(C:C,A:A,D2:E2,B:B,F2))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Peter_SSs

##### MrExcel MVP, Moderator
I agree that the Textjoin column you have may not be the simplest way to go, but if you want to stick with it, I think this should do what you want.

21 10 22.xlsm
ABCDEF
1WeekNumNameNo's of ChangeTextjoin for Two Weeks Criteria 1Criteria 2Sum Change Answer
21Zone-111&2Zone-13
31Zone-111&2Zone-22
42Zone-211&2Zone-33
52Zone-11
61Zone-31
73Zone-21
84Zone-11
92Zone-21
101Zone-31
112Zone-31
Lukma
Cell Formulas
RangeFormula
F2:F4F2=SUMPRODUCT(--ISNUMBER(FIND("&"&A\$2:A\$11&"&","&"&D2&"&")),--(B\$2:B\$11=E2),C\$2:C\$11)

#### Lukma

##### Board Regular
Hi Peter

Thanks so much but please i will appreciate with your assistance if there is better option as you have suggested this will add to my knowledge on excel

Please do let me know your option recommended to simplify it more

Thanks

#### Peter_SSs

##### MrExcel MVP, Moderator

Jason has given one possible way. Does that work for you?

Otherwise you might need to give us more of an idea of what you have and what you might be trying to achieve. So far we have just one small example - and column D has the same value in each populated row. Is that the actual case? If not, we need more of an idea about what you might have, especially in column D.

#### Lukma

##### Board Regular
HI Peter

Yes it really Amazing and it work just as i wanted, But please as you have suggested that there is a better way to go about it that i would also prefer to have a hint of the formula rather going through long process with the textjoin

i will appreciate if you can as well help out with the option which is lot easy and better incase i dont have to use a textjoin

Regards

#### Peter_SSs

##### MrExcel MVP, Moderator

If Jason's method does what you want & works fine & does not use textjoin, I don't know what extra you want.

BTW, what is the "long process with the textjoin"?

#### Lukma

##### Board Regular
good day
Hi Peter

sorry for the delay in replying back to you, i was actually not feeling too good lately few eyes problem,

Column B is the WkNum
Column E Number of Change Zone
Column M is a dropdown Month
In Column N i have my 2 Week Join as a dropdown
In Column O my Zone criteria listed
In Column P7 i need to know how many change made for each zone BI-weekly

Further Please if there is better option as you have suggested to go about the formula BI-weekly, it will be great to have that to my knowledge in excel
which will really help me a lot

Book1 (version 2).xlsb
BCDEFGHIJKLMNOP
14040 & 41
24142 & 43
342
443
5
6WeekNumMusVesselsNo's of ChangeLocationNo's Of Change in VslChange Date & Time Arrive Site Date & Time ZoneTotal Hours01-Oct-2142 & 43ZoneNos Change
10406QMS Delta1Rig Al Ghallan103/10/21 09:35Zone-2Zone-4
11416QMS Delta1Rig Al Hail04/10/21 15:45Zone-3
134110SEACOR ALPS1Rig Butinah 105/10/21 09:42Zone-3
164115A-LIBERTY1Deep Driller-3108/10/21 12:10
19418QMS NEPTUNE1Rig Yemillah110/10/21 22:50Zone-3
20428QMS NEPTUNE1Rig Al Gharbia13/10/21 08:47Zone-3
224233Z-POWER1Rig Junana113/10/21 17:45Zone-2
234222AMS-RUBY 1Rig SMS Faith 116/10/21 09:50Zone-2
244225MAC PHOENIX1Deep Driller-3116/10/21 09:50
254227QMS MARIMBA1Rig VKN-3117/10/21 14:50Zone-3
264227QMS MARIMBA1Rig Qarnin17/10/21 14:50Zone-3
Sheet1
Cell Formulas
RangeFormula
P1P1=N1&" & "&N2
P2P2=N3&" & "&N4
N1N1=WEEKNUM(M6)
N2:N4N2=N1+1
B7:B26B7=IF(H7<>"",WEEKNUM(H7,2),"")
J7:J26J7=IFERROR(IF(LEN(VLOOKUP(F7,Sheet2!\$B\$4:\$D\$74,3,0))=0,"",VLOOKUP(F7,Sheet2!\$B\$4:\$D\$74,3,0)),"")
Cells with Data Validation
CellAllowCriteria
M6List=Sheet2!\$F\$4:\$F\$195
N6List=\$P\$1:\$P\$2

#### Peter_SSs

##### MrExcel MVP, Moderator
One way would be to split the N6 drop-down value into its two numbers (I have done that in M7:N7 below) and then used Jason's formula.

Lukma.xlsm
BEJLMNOP
6WeekNumNo's of ChangeZone01-Oct-2142 & 43ZoneNos Change
7401Zone-24243Zone-11
8401Zone-3Zone-22
9401Zone-3Zone-33
10401Zone-2Zone-40
11411Zone-3
12411Zone-1
13411Zone-3
14411Zone-2
15411Zone-4
16411
17411Zone-3
18411Zone-3
19411Zone-3
20421Zone-3
21421Zone-1
22421Zone-2
23421Zone-2
24421
25421Zone-3
26421Zone-3
Sheet1
Cell Formulas
RangeFormula
M7M7=LEFT(N6,2)+0
N7N7=RIGHT(N6,2)+0
P7:P10P7=SUM(SUMIFS(E\$7:E\$26,B\$7:B\$26,M\$7:N\$7,J\$7:J\$26,O7))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Lukma

##### Board Regular
Dear Peter

Thanks so much its been a great help finding a solution to my spreadsheet data

Am much grateful

Replies
5
Views
149
Replies
4
Views
491
Replies
0
Views
126
Replies
2
Views
203
Replies
3
Views
865

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,571
Messages
5,770,920
Members
425,652
Latest member
Pemby

### 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.

### Which adblocker are you using?

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

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