index+match

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
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
ABCDEFGHI
51
52up-to-date2000
53dateopening quantityrental quantity (pre booked)rental quantity (actual)net balancereturn quantityclosing quantityremarks
54Saturday, 1 July 202320202020
55Sunday, 2 July 2023   
56Monday, 3 July 2023   
57Tuesday, 4 July 2023   
58Wednesday, 5 July 2023   
59Thursday, 6 July 2023   
60Friday, 7 July 2023   
61Saturday, 8 July 2023   
DLH - DATA
Cell Formulas
RangeFormula
G52,D52:E52D52=SUM(D54:D84)
D54D54=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:C61C55=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:F61F54=IFERROR((C54-E54),"")
H54:H61H54=IFERROR((C54-E54+G54),"")


source come from this page:
HOTEL.xlsx
ABCDE
51Saturday, 1 July 2023Sunday, 2 July 2023Monday, 3 July 2023
521guest name
53guest contact
54booking number
55number of stay
56pre booked quantity10
572guest name
58guest contact
59booking number
60number of stay
61pre booked quantity10
623guest name
63guest contact
64booking number
65number of stay
66pre booked quantity
674guest name
68guest contact
69booking number
70number of stay
71pre booked quantity
DLH - GUEST



THANK YOU VERY MUCH FOR YOUR GUIDANCE
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
How about
Excel Formula: 
=SUMIFS('DLH - GUEST'!C52:C151,'DLH - GUEST'!B52:B151,"pre booked quantity")
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=SUMIFS('DLH - GUEST'!C52:C151,'DLH - GUEST'!B52:B151,"pre booked quantity")
Click to expand...
this is real nice!!

however, do i need to use index+match, match with the date as well, just to prevent any wrong return?
i will have july to december in 2023, but january to december in next year

HOTEL.xlsx
ABC
51Saturday, 1 July 2023
521guest name
53guest contact
54booking number
55number of stay
56pre booked quantity
572guest name
58guest contact
59booking number
60number of stay
61pre booked quantity
623guest name
63guest contact
64booking number
65number of stay
66pre booked quantity
674guest name
68guest contact
69booking number
70number of stay
71pre booked quantity
725guest name
73guest contact
74booking number
75number of stay
76pre booked quantity
776guest name
78guest contact
79booking number
80number of stay
81pre booked quantity
827guest name
83guest contact
84booking number
85number of stay
86pre booked quantity
878guest name
88guest contact
89booking number
90number of stay
91pre booked quantity
929guest name
93guest contact
94booking number
95number of stay
96pre booked quantity
9710guest name
98guest contact
99booking number
100number of stay
101pre booked quantity
10211guest name
103guest contact
104booking number
105number of stay
106pre booked quantity
10712guest name
108guest contact
109booking number
110number of stay
111pre booked quantity
11213guest name
113guest contact
114booking number
115number of stay
116pre booked quantity
11714guest name
118guest contact
119booking number
120number of stay
121pre booked quantity
12215guest name
123guest contact
124booking number
125number of stay
126pre booked quantity
12716guest name
128guest contact
129booking number
130number of stay
131pre booked quantity
13217guest name
133guest contact
134booking number
135number of stay
136pre booked quantity
13718guest name
138guest contact
139booking number
140number of stay
141pre booked quantity
14219guest name
143guest contact
144booking number
145number of stay
146pre booked quantity
14720guest name
148guest contact
149booking number
150number of stay
151pre booked quantity
152
153
154
155Tuesday, 1 August 2023
1561guest name
157guest contact
158booking number
159number of stay
160pre booked quantity
1612guest name
162guest contact
163booking number
164number of stay
165pre booked quantity
1663guest name
167guest contact
168booking number
169number of stay
170pre booked quantity
1714guest name
172guest contact
173booking number
174number of stay
175pre booked quantity
1765guest name
177guest contact
178booking number
179number of stay
180pre booked quantity
1816guest name
182guest contact
183booking number
184number of stay
185pre booked quantity
1867guest name
187guest contact
188booking number
189number of stay
190pre booked quantity
1918guest name
192guest contact
193booking number
194number of stay
195pre booked quantity
1969guest name
197guest contact
198booking number
199number of stay
200pre booked quantity
20110guest name
202guest contact
203booking number
204number of stay
205pre booked quantity
20611guest name
207guest contact
208booking number
209number of stay
210pre booked quantity
21112guest name
212guest contact
213booking number
214number of stay
215pre booked quantity
21613guest name
217guest contact
218booking number
219number of stay
220pre booked quantity
22114guest name
222guest contact
223booking number
224number of stay
225pre booked quantity
22615guest name
227guest contact
228booking number
229number of stay
230pre booked quantity
23116guest name
232guest contact
233booking number
234number of stay
235pre booked quantity
23617guest name
237guest contact
238booking number
239number of stay
240pre booked quantity
24118guest name
242guest contact
243booking number
244number of stay
245pre booked quantity
24619guest name
247guest contact
248booking number
249number of stay
250pre booked quantity
25120guest name
252guest contact
253booking number
254number of stay
255pre booked quantity
DLH - GUEST
 
Upvote 0
Maybe
Excel Formula: 
=SUMIFS(INDEX('DLH - GUEST'!$C$52:$E$151,0,MATCH(D54,'DLH - GUEST'!$C$51:$E$151,0)),'DLH - GUEST'!$B$52:$B$151,"pre booked quantity")
 
Upvote 0
Fluff said:
Maybe
Excel Formula: 
=SUMIFS(INDEX('DLH - GUEST'!$C$52:$E$151,0,MATCH(D54,'DLH - GUEST'!$C$51:$E$151,0)),'DLH - GUEST'!$B$52:$B$151,"pre booked quantity")
Click to expand...
something wrong with below warning?

1689598129579.png
 
Upvote 0
Oops, it should be A54 in the match, not D54
 
Upvote 0
Fluff said:
Oops, it should be A54 in the match, not D54
Click to expand...
changed to A54, but #N/A?

HOTEL.xlsx
ABCDEFGHI
51
52up-to-date#N/A00
53dateopening quantityrental quantity (pre booked)rental quantity (actual)net balancereturn quantityclosing quantityremarks
54Saturday, 1 July 202320#N/A2020
55Sunday, 2 July 2023   
56Monday, 3 July 2023   
57Tuesday, 4 July 2023   
58Wednesday, 5 July 2023   
59Thursday, 6 July 2023   
DLH - DATA
Cell Formulas
RangeFormula
G52,D52:E52D52=SUM(D54:D84)
D54D54=SUMIFS(INDEX('DLH - GUEST'!$C$52:$E$151,0,MATCH(A54,'DLH - GUEST'!$C$51:$E$151,0)),'DLH - GUEST'!$B$52:$B$151,"pre booked quantity")
C55:C59C55=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:F59F54=IFERROR((C54-E54),"")
H54:H59H54=IFERROR((C54-E54+G54),"")


HOTEL.xlsx
ABC
51Saturday, 1 July 2023
521guest name
53guest contact
54booking number
55number of stay
56pre booked quantity1
572guest name
58guest contact
59booking number
60number of stay
61pre booked quantity
623guest name
63guest contact
64booking number
65number of stay
66pre booked quantity
674guest name
68guest contact
69booking number
70number of stay
71pre booked quantity
725guest name
73guest contact
74booking number
75number of stay
76pre booked quantity
DLH - GUEST
 
Upvote 0
Forgot to change the range in the match, it should be
Excel Formula: 
=SUMIFS(INDEX('DLH - GUEST'!$C$52:$E$151,0,MATCH(A54,'DLH - GUEST'!$C$51:$E$51,0)),'DLH - GUEST'!$B$52:$B$151,"pre booked quantity")
 
Upvote 0
Fluff said:
Forgot to change the range in the match, it should be
Excel Formula: 
=SUMIFS(INDEX('DLH - GUEST'!$C$52:$E$151,0,MATCH(A54,'DLH - GUEST'!$C$51:$E$51,0)),'DLH - GUEST'!$B$52:$B$151,"pre booked quantity")
Click to expand...
thanks Fluff, works great

last but not least, is this index+match can be applied to countifs?

HOTEL.xlsx
ABCD
51JULY 2023number of guestnumber of staypre booked quantity
522114
53Saturday, 1 July 20232114
54Sunday, 2 July 2023
DLH - SUMMERY
Cell Formulas
RangeFormula
B52:D52B52=SUM(B53:B83)
B53B53=COUNTIFS('DLH - GUEST'!$C$52:$C$151,"<>",'DLH - GUEST'!$B$52:$B$151,"guest name")
C53C53=SUMIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$E$51,0)),'DLH - GUEST'!$B$52:$B$151,$C$51)
D53D53=SUMIFS(INDEX('DLH - GUEST'!$C$52:$AG$151,0,MATCH(A53,'DLH - GUEST'!$C$51:$E$51,0)),'DLH - GUEST'!$B$52:$B$151,$D$51)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
RAINFALL CESSATION DATE USING INDEX
Replies
1
Views
237
Thlama
T
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
252
MeaclH
M
A
  • Question Question
Match/Lookup separate tables into one column
2
Replies
12
Views
1K
Skybluekid
Skybluekid
B
  • Question Question
INDEX MATCH Not returning #N/A error
Replies
5
Views
475
Fluff
Fluff
A
  • Question Question
To Find the difference of values through their crossing points on the Graph
Replies
2
Views
515
Fluff
Fluff

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

Share this page

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
Back
Top