calculating total

K

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
460
Office Version
  1. 2019
greeting to all
i need 2 formula on yellow to calculate the total quantity and schedule

i have no idea which should i use on column F(e.g sumif or sumproduct), or column G(e.g. countif) is better
can someone help?

thanks for the guidance

Book1
ABCDEFG
1datequantityschedulequantityschedule
2Friday, September 1, 20231√Friday, September 1, 202351
31Saturday, September 2, 2023102
41Sunday, September 3, 2023153
51Monday, September 4, 2023204
61Tuesday, September 5, 2023255
7Saturday, September 2, 20232√
82√
92
102
112
12Sunday, September 3, 20233√
133√
143√
153
163
17Monday, September 4, 20234√
184√
194√
204√
214
22Tuesday, September 5, 20235√
235√
245√
255√
265√
Sheet1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Is your sample data really representative of your real data? I suspect not since the following would produce the expected results but I'm sure it is not what you want.

What is the logic for getting the "schedule" values? Please explain in words.

23 09 12.xlsm
ABCDEFG
1datequantityschedulequantityschedule
2Friday, September 1, 20231√Friday, September 1, 202351
31Saturday, September 2, 2023102
41Sunday, September 3, 2023153
51Monday, September 4, 2023204
61Tuesday, September 5, 2023255
7Saturday, September 2, 20232√
82√
92
102
112
12Sunday, September 3, 20233√
133√
143√
153
163
17Monday, September 4, 20234√
184√
194√
204√
214
22Tuesday, September 5, 20235√
235√
245√
255√
265√
kelvin_9
Cell Formulas
RangeFormula
F2:F6F2=ROWS(F$2:F2)*5
G2:G6G2=ROWS(G$2:G2)
 
Upvote 0
Peter_SSs said:
Is your sample data really representative of your real data? I suspect not since the following would produce the expected results but I'm sure it is not what you want.

What is the logic for getting the "schedule" values? Please explain in words.

23 09 12.xlsm
ABCDEFG
1datequantityschedulequantityschedule
2Friday, September 1, 20231√Friday, September 1, 202351
31Saturday, September 2, 2023102
41Sunday, September 3, 2023153
51Monday, September 4, 2023204
61Tuesday, September 5, 2023255
7Saturday, September 2, 20232√
82√
92
102
112
12Sunday, September 3, 20233√
133√
143√
153
163
17Monday, September 4, 20234√
184√
194√
204√
214
22Tuesday, September 5, 20235√
235√
245√
255√
265√
kelvin_9
Cell Formulas
RangeFormula
F2:F6F2=ROWS(F$2:F2)*5
G2:G6G2=ROWS(G$2:G2)
Click to expand...
thank you very much for your reply, Peter_SSs

i am sincerely apologize for my poor english / describe

what i want is, i am doing some calculation on each day booking in my workbook

1) maximum 8 booking per day, i used "quantity" for booking and i used "schedule" for confirmation as easy reference
ps: let me change back to booking and schedule in below table
2) 6digits for each booking generated by report and my crew give "√" as confirmed if guest confrimed the booking finally, just leave it blank if the booking was no show / cancelled finally
3) so in my first 5 days example in september, i want a formula to return the subtotal of "booking #" and "confirmation" for each day

my guess, a sumif formula on column F for how many booking each day, and maybe a countif formula on column G for how many booking have been confirmed("√") by my crew
i have google searched for a lot(example: how to sumif in merged cell), but i cant get through it which i need someone guidance

how about this
Book1
ABCDEFG
1datebooking #confirmationdatebooking #confirmation
2Friday, 1 September 2023111111√Friday, 1 September 202311
3Saturday, 2 September 202332
4Sunday, 3 September 202353
5Monday, 4 September 202344
6Tuesday, 5 September 202355
7Saturday, 2 September 2023222222√
8222223√
9222224
10
11
12Sunday, 3 September 2023333333√
13333334√
14333335√
15333336
16333337
17Monday, 4 September 2023444444√
18444445√
19444446√
20444447√
21
22Monday, 5 June 2023555555√
23555556√
24555557√
25555558√
26555559√
Sheet1


thank you very much for everyone's guidance

have a great day
 
Upvote 0
I assume that those expected results are based on the final merged cell shown being 5 September, not 5 June as shown.
Try this. If the bookings are always filled from the top of each block then you could try the formula in column H but if there could be gaps within the bookings for a particular day then stick to the column G formula.

23 09 13.xlsm
ABCDEFGH
1datebooking #confirmationdatebooking #confirmationconfirmation
2Friday, 1 September 2023111111√Friday, 1 September 2023111
3Saturday, 2 September 2023322
4Sunday, 3 September 2023533
5Monday, 4 September 2023444
6Tuesday, 5 September 2023555
7Saturday, 2 September 2023222222√
8222223√
9222224
10
11
12Sunday, 3 September 2023333333√
13333334√
14333335√
15333336
16333337
17Monday, 4 September 2023444444√
18444445√
19444446√
20444447√
21
22Tuesday, 5 September 2023555555√
23555556√
24555557√
25555558√
26555559√
27
Sheet22
Cell Formulas
RangeFormula
F2:G6F2=COUNTA(INDEX(B:B,MATCH($E2,$A:$A,0)):INDEX(B:B,IFERROR(AGGREGATE(15,6,ROW(B$1:B$30)/(($A$1:$A$30<>"")*(ROW(B$1:B$30)>MATCH($E2,$A:$A,0))),1)-1,30)))
H2:H6H2=COUNTA(INDEX(C:C,MATCH($E2,$A:$A,0)):INDEX(C:C,MATCH($E2,$A:$A,0)+F2-1))
 
Upvote 0
Solution
Peter_SSs said:
I assume that those expected results are based on the final merged cell shown being 5 September, not 5 June as shown.
Try this. If the bookings are always filled from the top of each block then you could try the formula in column H but if there could be gaps within the bookings for a particular day then stick to the column G formula.

23 09 13.xlsm
ABCDEFGH
1datebooking #confirmationdatebooking #confirmationconfirmation
2Friday, 1 September 2023111111√Friday, 1 September 2023111
3Saturday, 2 September 2023322
4Sunday, 3 September 2023533
5Monday, 4 September 2023444
6Tuesday, 5 September 2023555
7Saturday, 2 September 2023222222√
8222223√
9222224
10
11
12Sunday, 3 September 2023333333√
13333334√
14333335√
15333336
16333337
17Monday, 4 September 2023444444√
18444445√
19444446√
20444447√
21
22Tuesday, 5 September 2023555555√
23555556√
24555557√
25555558√
26555559√
27
Sheet22
Cell Formulas
RangeFormula
F2:G6F2=COUNTA(INDEX(B:B,MATCH($E2,$A:$A,0)):INDEX(B:B,IFERROR(AGGREGATE(15,6,ROW(B$1:B$30)/(($A$1:$A$30<>"")*(ROW(B$1:B$30)>MATCH($E2,$A:$A,0))),1)-1,30)))
H2:H6H2=COUNTA(INDEX(C:C,MATCH($E2,$A:$A,0)):INDEX(C:C,MATCH($E2,$A:$A,0)+F2-1))
Click to expand...
thank you very much for your reply, Peter_SSs

sorry for the late reply and sorry for the typo mistake of "june"

formula was testified and all good, really amazing

thanks for the guidance
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Calculate Difference Actual and Budget Based on Date
Replies
3
Views
535
etaf
etaf
K
  • Question Question
sumif / countif
Replies
1
Views
267
kelvin_9
K
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
K
  • Solved
Calculate Total Expense With Condition
2
Replies
10
Views
507
Cubist
Cubist
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
281
Cquake
C

Forum statistics

Threads
1,223,944
Messages
6,175,554
Members
452,652
Latest member
eduedu

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