Formula to sum a text join based on multiple Criteria

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
14,631
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
7407ADNOC-10111Al Ghallan Island 202/10/21 19:25Zone-2Zone-1
8407ADNOC-10111Bu Sikeen Island03/10/21 09:50Zone-3Zone-2
9407ADNOC-10111Al Qatia Island 03/10/21 14:25Zone-3Zone-3
10406QMS Delta1Rig Al Ghallan103/10/21 09:35Zone-2Zone-4
11416QMS Delta1Rig Al Hail04/10/21 15:45Zone-3
12419ADNOC-2211Rig SMS Mariam 104/10/21 19:32Zone-1
134110SEACOR ALPS1Rig Butinah 105/10/21 09:42Zone-3
144111ADNOC-2291Rig Junana205/10/21 14:05Zone-2
154111ADNOC-2291Deep Driller-606/10/21 14:47Zone-4
164115A-LIBERTY1Deep Driller-3108/10/21 12:10 
174114ADNOC-2301Rig VKN-3208/10/21 12:10Zone-3
184114ADNOC-2301Rig Makasib10/10/21 22:50Zone-3
19418QMS NEPTUNE1Rig Yemillah110/10/21 22:50Zone-3
20428QMS NEPTUNE1Rig Al Gharbia13/10/21 08:47Zone-3
214218ADNOC-2251Rig Muhaiyimat113/10/21 13:28Zone-1
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
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
133
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Dear Peter

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

Am much grateful
 
Master Excel Bundle

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.

Forum statistics

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