Spreadsheet: 116011d1311476860-function-for-the-sum-of-occurances-in-multiple-specfic-ranges-datasheet
Sheet: datasheet

 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Question 1: What function(s) should I use in "BOX 1" to find the sums for cells O4-Q6?  i.e. For O4 Im looking to find a function that will provide the sum of occurances during "daytime" (8:00:00AM-6:00:00PM), which are Best Values (when best values are: ≤ 20% of the winners total)
 |  |  |  |  |  |  |  |  |  |  | BOX 1: |  |  | Best Values | Fair Values | Insufficent Values
*Note:  Color Legend Percentages are based on "Winnerrs Total", where $500.00 = 100% |  |  | Price | Day | Time | Date | # | # | # | Winners Total Cost |  |  | [NAME]
 |  |  |  |  |  |  |  |  |  |  |  |  | Nightime
 |  |  | 56.03 |  | 1900-01-15 09:50:24 | 2011-06-21 00:00:00 | 465 | 0.38 |  |  |  |  | Total
Color Legend: |  |  | 73.79 |  | 00:20:00 | 2011-06-22 00:00:00 | 210 | 0.42 |  |  |  |  |  |  |  |  |  | *Note: Im trying to figure out the most effcient functions, so that I can still use box 1 even if I change values D6-K71 (more specifically, I plan on regularly interchanging values in the columns D and also columns F-I)
Best Value ≤ 20% of Retail Value | Minimum 80% Discount |  | 79.8 |  | 16:20:00 | 2011-06-22 00:00:00 | 585 | 0.4
Fair Value = 21% - 49% of Retail Value | 51% - 79% Discount |  | 95.9 |  | 23:00:00 | 2011-06-22 00:00:00 | 569 | 0.34
Insufficent Value ≥ 50% of Retail Value | Maximum 50% Discount |  | 5.39 |  | 00:33:00 | 2011-06-23 00:00:00 | 59 | 0.61
[NAME] (8am-6pm) |  |  | 95.15 |  | 17:31:00 | 2011-06-23 00:00:00 | 1407 | 0.52 |  |  | BOX 2: | Days of Week: | Total Days: | Total BV: | Total FV: | Total IV:
Nightime (7pm-7am) |  |  | 29.47 |  | 11:30:00 | 2011-06-24 00:00:00 | 105 | 0.43
 |  |  | 98.93 |  | 18:26:00 | 2011-06-24 00:00:00 | 193 | 0.37 |  |  |  | Monday |  |  |  |  |  | Question 2:  What function(s) should I use in "BOX 2" to find the sums for cells O13-Q19  i.e. For O13 I am looking for the sum of occurances for Mondays which are Best Values (when best values are: ≤ 20% of the winners total)
 |  |  | 25.59 |  | 01:44:00 | 2011-06-25 00:00:00 | 915 | 0.27 |  |  |  | Tuesday
 |  |  | 106.31 |  | 16:50:00 | 2011-06-25 00:00:00 | 845 | 0.35 |  |  |  | Wednesday
 |  |  | 41.35 |  | 02:25:00 | 2011-06-26 00:00:00 | 472 | 0.43 |  |  |  | Thursday
 |  |  | 13.44 |  | 14:53:00 | 2011-06-26 00:00:00 | 192 | 0.34 |  |  |  | Friday
 |  |  | 31.67 |  | 21:08:00 | 2011-06-26 00:00:00 | 937 | 0.31 |  |  |  | Saturday |  |  |  |  |  | *Note: Im trying to figure out the most effcient functions, so that I can still use box 2 even if I change values D6-K71 (more specifically, I plan on regularly interchanging values in the columns D and also columns F-I)
 |  |  | 38 |  | 03:28:00 | 2011-06-27 00:00:00 | 126 | 0.3 |  |  |  | Sunday
 |  |  | 72.3 |  | 15:43:00 | 2011-06-27 00:00:00 | 172 | 0.46
 |  |  | 12.28 |  | 10:32:00 | 2011-06-28 00:00:00 | 84 | 0.46
 |  |  | 90.76 |  | 16:23:00 | 2011-06-28 00:00:00 | 1426 | 0.35
 |  |  | 99.24 |  | 00:31:00 | 2011-06-29 00:00:00 | 1967 | 0.3
 |  |  | 8.92 |  | 14:32:00 | 2011-06-29 00:00:00 | 139 | 0.28
 |  |  | 73.76 |  | 22:34:00 | 2011-06-29 00:00:00 | 8 | 0.19
 |  |  | 60.32 |  | 04:05:00 | 2011-06-30 00:00:00 | 1744 | 0.27
 |  |  | 23.1 |  | 15:42:00 | 2011-06-30 00:00:00 | 438 | 0.32
 |  |  | 83.4 |  | 12:05:00 | 2011-07-01 00:00:00 | 1020 | 0.36
 |  |  | 9.59 |  | 16:25:00 | 2011-07-01 00:00:00 | 22 | 0.36
 |  |  | 2.77 |  | 11:13:00 | 2011-07-02 00:00:00 | 68 | 0.38
 |  |  | 52.36 |  | 18:13:00 | 2011-07-02 00:00:00 | 1133 | 0
 |  |  | 124.76 |  | 18:13:00 | 2011-07-04 00:00:00 | 28475 | 0
 |  |  | 10.34 |  | 14:36:00 | 2011-07-05 00:00:00 | 135 | 0.36
 |  |  | 73.84 |  | 00:40:00 | 2011-07-06 00:00:00 | 772 | 0.33
 |  |  | 8.04 |  | 11:55:00 | 2011-07-06 00:00:00 | 116 | 0.35
 |  |  | 98.57 |  | 16:19:00 | 2011-07-07 00:00:00 | 430 | 0.26
 |  |  | 39.77 |  | 15:48:00 | 2011-07-08 00:00:00 | 300 | 0.34
 |  |  | 144.51 |  | 17:53:00 | 2011-07-09 00:00:00 | 469 | 0.46
 |  |  | 7.56 |  | 14:24:00 | 2011-07-10 00:00:00 | 110 | 0.32
 |  |  | 37.21 |  | 21:44:00 | 2011-07-10 00:00:00 | 38 | 0.43
 |  |  | 130.7 |  | 17:49:00 | 2011-07-11 00:00:00 | 836 | 0.35
 |  |  | 8.94 |  | 19:43:00 | 2011-07-11 00:00:00 | 197 | 0.46
 |  |  | 55.81 |  | 01:45:00 | 2011-07-12 00:00:00 | 600 | 0.29
 |  |  | 67.49 |  | 16:00:00 | 2011-07-12 00:00:00 | 488 | 0.44
 |  |  | 89.68 |  | 22:52:00 | 2011-07-12 00:00:00 | 1627 | 0.51
 |  |  | 27.6 |  | 15:15:00 | 2011-07-13 00:00:00 | 529 | 0.4
 |  |  | 86.23 |  | 01:19:00 | 2011-07-14 00:00:00 | 57 | 0.39
 |  |  | 30.91 |  | 03:04:00 | 2011-07-14 00:00:00 | 132 | 0.32
 |  |  | 34.65 |  | 15:38:00 | 2011-07-14 00:00:00 | 284 | 0.37
 |  |  | 11.28 |  | 19:42:00 | 2011-07-14 00:00:00 | 9 | 0.16
 |  |  | 84.29 |  | 02:24:00 | 2011-07-15 00:00:00 | 1146 | 0.37
 |  |  | 46.04 |  | 12:40:00 | 2011-07-15 00:00:00 | 353 | 0.34
 |  |  | 21.76 |  | 17:36:00 | 2011-07-15 00:00:00 | 180 | 0.39
 |  |  | 32.26 |  | 01:23:00 | 2011-07-16 00:00:00 | 739 | 0.31
 |  |  | 72.33 |  | 16:08:00 | 2011-07-16 00:00:00 | 272 | 0.35
 |  |  | 24.16 |  | 21:27:00 | 2011-07-16 00:00:00 | 230 | 0.29
 |  |  | 64.69 |  | 02:34:00 | 2011-07-17 00:00:00 | 1317 | 0.39
 |  |  | 22.69 |  | 15:41:00 | 2011-07-17 00:00:00 | 35 | 0.73
 |  |  | 34.19 |  | 00:21:00 | 2011-07-18 00:00:00 | 130 | 0.45
 |  |  | 18.59 |  | 15:06:00 | 2011-07-18 00:00:00 | 431 | 0.47
 |  |  | 45.91 |  | 20:50:00 | 2011-07-18 00:00:00 | 853 | 0.59
 |  |  | 65.76 |  | 01:27:00 | 2011-07-19 00:00:00 | 1443 | 0.27
 |  |  | 157.9 |  | 15:53:00 | 2011-07-19 00:00:00 | 376 | 0.38
 |  |  | 7.04 |  | 16:16:00 | 2011-07-19 00:00:00 | 195 | 0.33
 |  |  | 2.28 |  | 03:30:00 | 2011-07-20 00:00:00 | 85 | 0.4
 |  |  | 65.47 |  | 16:53:00 | 2011-07-20 00:00:00 | 517 | 0.16
 |  |  | 2.76 |  | 00:15:00 | 2011-07-21 00:00:00 | 126 | 0.34
 |  |  | 42.45 |  | 14:47:00 | 2011-07-21 00:00:00 | 1176 | 0.38
 |  |  | 13.57 |  | 22:27:00 | 2011-07-21 00:00:00 | 622 | 0.53
 |  |  | 51.49 |  | 11:26:00 | 2011-07-22 00:00:00 | 613 | 0.4
 |  |  | 51.55 |  | 00:58:00 | 2011-07-23 00:00:00 | 827 | 0.29