Formula to sum a text join based on multiple Criteria

[Lukma]

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

WeekNum	Name	No's of Change	Textjoin for Two Weeks Criteria 1	Criteria 2	Sum Change Answer
1	Zone-1	1	1&2	Zone-1
1	Zone-1	1	1&2	Zone-2
2	Zone-2	1	1&2	Zone-3
2	Zone-1	1
1	Zone-3	1
3	Zone-2	1
4	Zone-1	1
2	Zone-2	1
1	Zone-3	1
2	Zone-3	1

 

[jasonb75]

Don't use textjoin, have 2 separate columns, 1 for each of the 2 week numbers. Then it's a simple sumifs formula.

Book1
	A	B	C	D	E	F	G	H
1	WeekNum	Name	No's of Change	Criteria 1 1st week	Criteria 1 2nd week	Criteria 2	Sum Change Answer
2	1	Zone-1	1	1	2	Zone-1	3
3	1	Zone-1	1	1	2	Zone-2	2
4	2	Zone-2	1	1	2	Zone-3	3
5	2	Zone-1	1
6	1	Zone-3	1
7	3	Zone-2	1
8	4	Zone-1	1
9	2	Zone-2	1
10	1	Zone-3	1
11	2	Zone-3	1
Sheet3

Cell Formulas
Range	Formula
G2:G4	G2	=SUM(SUMIFS(C:C,A:A,D2:E2,B:B,F2))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

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
	A	B	C	D	E	F
1	WeekNum	Name	No's of Change	Textjoin for Two Weeks Criteria 1	Criteria 2	Sum Change Answer
2	1	Zone-1	1	1&2	Zone-1	3
3	1	Zone-1	1	1&2	Zone-2	2
4	2	Zone-2	1	1&2	Zone-3	3
5	2	Zone-1	1
6	1	Zone-3	1
7	3	Zone-2	1
8	4	Zone-1	1
9	2	Zone-2	1
10	1	Zone-3	1
11	2	Zone-3	1
Lukma

Cell Formulas
Range	Formula
F2:F4	F2	=SUMPRODUCT(--ISNUMBER(FIND("&"&A$2:A$11&"&","&"&D2&"&")),--(B$2:B$11=E2),C$2:C$11)

 

[Lukma]

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]

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]

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]

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]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1													40		40 & 41
2													41		42 & 43
3													42
4													43
5
6	WeekNum	Mus	Vessels	No's of Change	Location	No's Of Change in Vsl	Change Date & Time	Arrive Site Date & Time	Zone	Total Hours		01-Oct-21	42 & 43	Zone	Nos Change
7	40	7	ADNOC-1011	1	Al Ghallan Island	2	02/10/21 19:25		Zone-2					Zone-1
8	40	7	ADNOC-1011	1	Bu Sikeen Island		03/10/21 09:50		Zone-3					Zone-2
9	40	7	ADNOC-1011	1	Al Qatia Island		03/10/21 14:25		Zone-3					Zone-3
10	40	6	QMS Delta	1	Rig Al Ghallan	1	03/10/21 09:35		Zone-2					Zone-4
11	41	6	QMS Delta	1	Rig Al Hail		04/10/21 15:45		Zone-3
12	41	9	ADNOC-221	1	Rig SMS Mariam	1	04/10/21 19:32		Zone-1
13	41	10	SEACOR ALPS	1	Rig Butinah	1	05/10/21 09:42		Zone-3
14	41	11	ADNOC-229	1	Rig Junana	2	05/10/21 14:05		Zone-2
15	41	11	ADNOC-229	1	Deep Driller-6		06/10/21 14:47		Zone-4
16	41	15	A-LIBERTY	1	Deep Driller-3	1	08/10/21 12:10
17	41	14	ADNOC-230	1	Rig VKN-3	2	08/10/21 12:10		Zone-3
18	41	14	ADNOC-230	1	Rig Makasib		10/10/21 22:50		Zone-3
19	41	8	QMS NEPTUNE	1	Rig Yemillah	1	10/10/21 22:50		Zone-3
20	42	8	QMS NEPTUNE	1	Rig Al Gharbia		13/10/21 08:47		Zone-3
21	42	18	ADNOC-225	1	Rig Muhaiyimat	1	13/10/21 13:28		Zone-1
22	42	33	Z-POWER	1	Rig Junana	1	13/10/21 17:45		Zone-2
23	42	22	AMS-RUBY	1	Rig SMS Faith	1	16/10/21 09:50		Zone-2
24	42	25	MAC PHOENIX	1	Deep Driller-3	1	16/10/21 09:50
25	42	27	QMS MARIMBA	1	Rig VKN-3	1	17/10/21 14:50		Zone-3
26	42	27	QMS MARIMBA	1	Rig Qarnin		17/10/21 14:50		Zone-3
Sheet1

Cell Formulas
Range	Formula
P1	P1	=N1&" & "&N2
P2	P2	=N3&" & "&N4
N1	N1	=WEEKNUM(M6)
N2:N4	N2	=N1+1
B7:B26	B7	=IF(H7<>"",WEEKNUM(H7,2),"")
J7:J26	J7	=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
Cell	Allow	Criteria
M6	List	=Sheet2!$F$4:$F$195
N6	List	=$P$1:$P$2

 

[Peter_SSs]

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
	B			E					J		L	M	N	O	P
6	WeekNum			No's of Change					Zone			01-Oct-21	42 & 43	Zone	Nos Change
7	40			1					Zone-2			42	43	Zone-1	1
8	40			1					Zone-3					Zone-2	2
9	40			1					Zone-3					Zone-3	3
10	40			1					Zone-2					Zone-4	0
11	41			1					Zone-3
12	41			1					Zone-1
13	41			1					Zone-3
14	41			1					Zone-2
15	41			1					Zone-4
16	41			1
17	41			1					Zone-3
18	41			1					Zone-3
19	41			1					Zone-3
20	42			1					Zone-3
21	42			1					Zone-1
22	42			1					Zone-2
23	42			1					Zone-2
24	42			1
25	42			1					Zone-3
26	42			1					Zone-3
Sheet1

Cell Formulas
Range	Formula
M7	M7	=LEFT(N6,2)+0
N7	N7	=RIGHT(N6,2)+0
P7:P10	P7	=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]

Dear Peter

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

Am much grateful