Help With Formula Sumifs based on Two criteria Range

[Lukma]

Hi good friend

I have slight issue getting around with the formula i have in column D7 to W7 with different MUS-1, or MUS-2 and so on and i have in column B10:B100 with List name Now i need formula to Sum value in Column X10 base on criteria range in column D7:W7 and based on Column B10:B100 i need formula result in Column D25 for each Criteria

Appreciate your help and support always

Regards

24_hours Vessel Utilization Report.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
7		VOYAGE (MUS NO)	Mus-1							Mus-3					Mus-4					Mus-6
8		Total Time in Days 24Hrs	22.900					0.000					0.000										23	0.954167
9		Units (Rigs / Barges)	1					2					3										Total Hour	In Days
10	Working MOSB	Working MOSB Base Jetty Cargo Operation	1.0																				1.0	0.0416667
11	Waiting MOSB permission	Waiting for ADNOC Permission FWB	2.3																				2.3	0.0958333
12	Waiting MOSB	Waiting ADNOC Berthing Instruction			5.2																		5.2	0.2166667
13	Waiting MOSB	Waiting Base Cargo Operation																					0.0	0
14	Waiting MOSB	Waitng Drilling Material																					0.0	0
15	Waiting MOSB	Cargo Operation Suspended																					0.0	0
16	Waitng VTS	Waiting VTS permission			13.4																		13.4	0.5583333
17	Waitng VTS	Waiting on Channel Closure			1																		1.0	0.0416667
18	Waiting Pilot	Waiting for Pilot																					0.0	0
19	WOW	Waiting WOW , FOG / POOR VISIBILITY																					0.0	0
20	Shifting MOSB Berth	Shifting Berth ADNOC Port																					0.0	0
21	Steaming	InTransit / Steaming to Location																					0.0	0
22
23
24			Working MOSB	Waiting MOSB permission	Waiting MOSB	Waiting MOSB	Waiting MOSB	Waiting MOSB	Waitng VTS	Waitng VTS	Waiting Pilot	WOW	Shifting MOSB Berth	Steaming
25		Mus-1
26		Mus-3
27		Mus-4
28		Mus-6
Sheet3

Cell Formulas
Range	Formula
D8,I8,N8	D8	=SUM(D10:H262)
X8,X10:X21	X8	=SUM(D8:W8)
Y8,Y10:Y21	Y8	=X8/24

 

[awoohaw]

Not sure if this is what you want.
It would help if you gave some expected values in the bottom half. Additionally, I used column C as the lookup instead of column B.

Book1
		B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
7			VOYAGE (MUS NO)	Mus-1							Mus-3					Mus-4					Mus-6
8			Total Time in Days 24Hrs	31.4					14.4					0										45.8	1.908333333
9			Units (Rigs / Barges)	1					2					3										Total Hour	In Days
10		Working MOSB	Working MOSB Base Jetty Cargo Operation	1																				1	0.041666667
11		Waiting MOSB permission	Waiting for ADNOC Permission FWB	2.3																				2.3	0.095833333
12		Waiting MOSB	Waiting ADNOC Berthing Instruction			5.2																		5.2	0.216666667
13		Waiting MOSB	Waiting Base Cargo Operation																					0	0
14		Waiting MOSB	Waitng Drilling Material																					0	0
15		Waiting MOSB	Cargo Operation Suspended																					0	0
16		Waitng VTS	Waiting VTS permission			13.4																		13.4	0.558333333
17		Waitng VTS	Waiting on Channel Closure			1																		1	0.041666667
18		Waiting Pilot	Waiting for Pilot																					0	0
19		WOW	Waiting WOW , FOG / POOR VISIBILITY																					0	0
20		Shifting MOSB Berth	Shifting Berth ADNOC Port																					0	0
21		Steaming	InTransit / Steaming to Location																					0	0
22
23
24				Working MOSB Base Jetty Cargo Operation	Waiting for ADNOC Permission FWB	Waiting ADNOC Berthing Instruction	Waiting Base Cargo Operation	Waitng Drilling Material	Cargo Operation Suspended	Waiting VTS permission	Waiting on Channel Closure	Waiting for Pilot	Waiting WOW , FOG / POOR VISIBILITY	Shifting Berth ADNOC Port	InTransit / Steaming to Location
25			Mus-1	1	2.3	5.2	0	0	0	13.4	1	0	0	0	0
26			Mus-3	0	0	0	0	0	0	0	0	0	0	0	0
27			Mus-4	0	0	0	0	0	0	0	0	0	0	0	0
28			Mus-6	0	0	0	0	0	0	0	0	0	0	0	0
29
30
31
Sheet2

Cell Formulas
Range	Formula
D8,N8,I8	D8	=SUM(D10:H262)
X8,X10:X21	X8	=SUM(D8:W8)
Y8,Y10:Y21	Y8	=X8/24
D25:O28	D25	=SUM(INDEX(SWITCH($C25,"Mus-1",$D$10:$J$21,"Mus-3",$K$10:$O$21,"Mus-4",$P$10:$T$21,"Mus-6",$U$10:$W$21),MATCH(D$24,$C$10:$C$21,0),0))

 

[Lukma]

Awoohaw

may than it works just i needed it.

so grateful
Regards

 

[awoohaw]

Happy to help.
Best Wishes.