agentkramr
Board Regular
- Joined
- Dec 27, 2021
- Messages
- 98
- Platform
- Windows
i currently have a spreadsheet that is connected to an oracle database
i am using a SUMIF to populate the total amount of attendance on the 2018 1 hour counts sheet right next to the times from the tab called attendance BUT if i want just the ones that are 74.99 and 39.99 to calculate price wise but opnly for thew date what doi i need to do ? everything else would calculate to regular
WCI HMTN VIP.xlsm | |||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | |||
1 | 11/4/21 11:24 AM | 11/5/2020 11:24 | 11:00 AM | 2:00 PM | 5:00 PM | 8:00 PM | 6:00 PM | ||||||||
2 | |||||||||||||||
3 | |||||||||||||||
4 | Time | 2022 | |||||||||||||
5 | |||||||||||||||
6 | |||||||||||||||
7 | Friday, November 4, 2022 | 11:00 AM | 0 | ||||||||||||
8 | VIP | 0 | |||||||||||||
9 | Regular | ||||||||||||||
10 | 2:00 PM | 0 | |||||||||||||
11 | VIP | ||||||||||||||
12 | Regular | ||||||||||||||
13 | 5:00 PM | 391 | |||||||||||||
14 | VIP | ||||||||||||||
15 | Regular | ||||||||||||||
16 | 8:00 PM | 142 | 533 | ||||||||||||
17 | VIP | ||||||||||||||
18 | Regular | ||||||||||||||
2018 1 Hour Counts |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A1 | A1 | =NOW()-364 |
H1 | H1 | =NOW()-728 |
A7 | A7 | =TODAY()+1 |
B7 | B7 | =A7+I$1 |
C7,C16,C13,C10 | C7 | =SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$I) |
C8 | C8 | =SUMIF(ATTENDANCE!$B:$B,'2018 1 Hour Counts'!B7,ATTENDANCE!$I:$J) |
B10 | B10 | =A7+J$1 |
B13 | B13 | =A7+K$1 |
B16 | B16 | =A7+L$1 |
D16 | D16 | =SUM(C7:C16) |
WCI HMTN VIP.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | EVENT_CODE | EVENT_DATE | ATTENDANCE | PRICE | DATE | TIME | FORMULA | CALENDAR_FORMAT | VLOOKUP_ATTENDANCE | Column1 | ||
2 | 22HM1101A | 11/1/2022 17:00 | 1 | 30.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 1 | 30.99 | ||
3 | 22HM1101A | 11/1/2022 17:00 | 5 | 32.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 5 | 32.99 | ||
4 | 22HM1101A | 11/1/2022 17:00 | 4 | 33.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 4 | 33.99 | ||
5 | 22HM1101A | 11/1/2022 17:00 | 12 | 34.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 12 | 34.99 | ||
6 | 22HM1101A | 11/1/2022 17:00 | 3 | 39.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 3 | 39.99 | ||
7 | 22HM1101A | 11/1/2022 17:00 | 8 | 54.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 8 | 54.99 | ||
8 | 22HM1101A | 11/1/2022 17:00 | 4 | 57.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 4 | 57.99 | ||
9 | 22HM1101A | 11/1/2022 17:00 | 48 | 59.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 48 | 59.99 | ||
10 | 22HM1101A | 11/1/2022 17:00 | 8 | 62.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 8 | 62.99 | ||
11 | 22HM1101A | 11/1/2022 17:00 | 72 | 64.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 72 | 64.99 | ||
12 | 22HM1101A | 11/1/2022 17:00 | 11 | 65.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 11 | 65.99 | ||
13 | 22HM1101A | 11/1/2022 17:00 | 95 | 66.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 95 | 66.99 | ||
14 | 22HM1101A | 11/1/2022 17:00 | 229 | 69.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 229 | 69.99 | ||
15 | 22HM1101A | 11/1/2022 17:00 | 23 | 74.99 | 11/1/2022 | 0.708333333 | 0 | #REF! | 23 | 74.99 | ||
ATTENDANCE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E15 | E2 | =INT([@[EVENT_DATE]]) |
F2:F15 | F2 | =[@[EVENT_DATE]]-INT([@[EVENT_DATE]]) |
G2:G15 | G2 | =SUMIFS([ATTENDANCE],[DATE],">=2/1/2019",[DATE],"<=2/28/2019") |
H2:H15 | H2 | =CONCATENATE(#REF!, " ", TEXT(F2, "HAM/PM"), " ", [@ATTENDANCE]) |
I2:I15 | I2 | =[@ATTENDANCE] |
J2:J15 | J2 | =[@PRICE] |
i am using a SUMIF to populate the total amount of attendance on the 2018 1 hour counts sheet right next to the times from the tab called attendance BUT if i want just the ones that are 74.99 and 39.99 to calculate price wise but opnly for thew date what doi i need to do ? everything else would calculate to regular