index+match

[kelvin_9]

HI All,

to simplfy my formula at cell D54, is there any better formula(index+match) i can use to return the result?

HOTEL.xlsx
	A	B	C	D	E	F	G	H	I
51
52	up-to-date			20	0		0
53	date		opening quantity	rental quantity (pre booked)	rental quantity (actual)	net balance	return quantity	closing quantity	remarks
54	Saturday, 1 July 2023		20	20		20		20
55	Sunday, 2 July 2023
56	Monday, 3 July 2023
57	Tuesday, 4 July 2023
58	Wednesday, 5 July 2023
59	Thursday, 6 July 2023
60	Friday, 7 July 2023
61	Saturday, 8 July 2023
DLH - DATA

Cell Formulas
Range	Formula
G52,D52:E52	D52	=SUM(D54:D84)
D54	D54	=SUM('DLH - GUEST'!C56,'DLH - GUEST'!C61,'DLH - GUEST'!C66,'DLH - GUEST'!C71,'DLH - GUEST'!C76,'DLH - GUEST'!C81,'DLH - GUEST'!C86,'DLH - GUEST'!C91,'DLH - GUEST'!C96,'DLH - GUEST'!C101,'DLH - GUEST'!C106,'DLH - GUEST'!C111,'DLH - GUEST'!C116,'DLH - GUEST'!C121,'DLH - GUEST'!C126,'DLH - GUEST'!C131,'DLH - GUEST'!C136,'DLH - GUEST'!C141,'DLH - GUEST'!C146,'DLH - GUEST'!C151)
C55:C61	C55	=IF(M54="","",IF(M54="no",H54,IF(OR(L54="/"),$C$54,IF(AND(M54="yes",L54="park to hotel"),(H54+K54),IF(AND(M54="yes",L54="return to park"),(H54+K54))))))
F54:F61	F54	=IFERROR((C54-E54),"")
H54:H61	H54	=IFERROR((C54-E54+G54),"")

source come from this page:

HOTEL.xlsx
	A	B	C	D	E
51			Saturday, 1 July 2023	Sunday, 2 July 2023	Monday, 3 July 2023
52	1	guest name
53		guest contact
54		booking number
55		number of stay
56		pre booked quantity	10
57	2	guest name
58		guest contact
59		booking number
60		number of stay
61		pre booked quantity	10
62	3	guest name
63		guest contact
64		booking number
65		number of stay
66		pre booked quantity
67	4	guest name
68		guest contact
69		booking number
70		number of stay
71		pre booked quantity
DLH - GUEST

THANK YOU VERY MUCH FOR YOUR GUIDANCE

 

[kelvin_9]

Yes, in exactly the same way as for the sumifs.

something wrong?

HOTEL.xlsx
	A	B	C	D
51	JULY 2023	number of guest	number of stay	pre booked quantity
52		0	10	100
53	Saturday, 1 July 2023	0	10	100
DLH - SUMMERY

Cell Formulas
Range	Formula
B52:D52	B52	=SUM(B53:B83)
B53	B53	=COUNTIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$AG$51,0)),"guest name")
C53	C53	=SUMIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$AG$51,0)),'DLH - GUEST'!$B$52:$B$151,C$51)
D53	D53	=SUMIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$AG$51,0)),'DLH - GUEST'!$B$52:$B$151,D$51)

HOTEL.xlsx
	A	B	C
51			Saturday, 1 July 2023
52	1	guest name	a
53		guest contact
54		booking number
55		number of stay	10
56		pre booked quantity	100
DLH - GUEST

 

[Fluff]

It should be

=COUNTIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$AG$51,0)),"<>",'DLH - GUEST'!$B$52:$B$151,"guest name")

 

[kelvin_9]

It should be

=COUNTIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$AG$51,0)),"<>",'DLH - GUEST'!$B$52:$B$151,"guest name")

Fluff, thank you very much for your kind help

 

[Fluff]

You're welcome & thanks for the feedback.