I have what I'm assuming is a straightforward formula challenge that I can't seem to figure out.
I have a table of data with the headers "Date,START TIME, END TIME, USAGE, UNITS, COST". The START TIME and END TIME is an hour block of time (e.g., 0:00 & 0:59, up to 23:00 & 23:59).
In adjacent cells I have, say J17 & J18 with a pull down list of dates, that I want to use in a "greater than or equal to 02/01/2021 and less than or equal to 02/28/2021, in a referencing sub table if you will immediately adjacent to these date parameters, listed as "START TIME", "COST", and "USAGE".
In the cells under this sub table, I want to place formulas that do this:
* Given the date interval cells, search the main table's START TIME (categories = hour blocks) and sum the values for COST and USAGE.
I have a table of data with the headers "Date,START TIME, END TIME, USAGE, UNITS, COST". The START TIME and END TIME is an hour block of time (e.g., 0:00 & 0:59, up to 23:00 & 23:59).
In adjacent cells I have, say J17 & J18 with a pull down list of dates, that I want to use in a "greater than or equal to 02/01/2021 and less than or equal to 02/28/2021, in a referencing sub table if you will immediately adjacent to these date parameters, listed as "START TIME", "COST", and "USAGE".
In the cells under this sub table, I want to place formulas that do this:
* Given the date interval cells, search the main table's START TIME (categories = hour blocks) and sum the values for COST and USAGE.
Electricity Usage Cost 07-05-2020 to Present.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | |||
1 | Name | ||||||||||||||||||||
2 | Address | ||||||||||||||||||||
3 | Account Number | ||||||||||||||||||||
4 | Service | ||||||||||||||||||||
5 | |||||||||||||||||||||
6 | TYPE | DATE | START TIME | END TIME | USAGE | UNITS | COST | NOTES | Compare | DATE | Day | Electric Use | Cost | Unit | |||||||
7 | Electric usage | 7/6/20 | 0:00 | 0:59 | 0.37 | kWh | 0.09 | 1st Day | 2/1/21 | Mon | 35.07 | $8.75 | kwh | ||||||||
8 | Electric usage | 7/6/20 | 1:00 | 1:59 | 0.21 | kWh | 0.05 | 2nd Day | 2/8/21 | Mon | 35.92 | $11.28 | kwh | ||||||||
9 | Electric usage | 7/6/20 | 2:00 | 2:59 | 0.16 | kWh | 0.04 | ||||||||||||||
10 | Electric usage | 7/6/20 | 3:00 | 3:59 | 0.17 | kWh | 0.04 | ||||||||||||||
11 | Electric usage | 7/6/20 | 4:00 | 4:59 | 0.15 | kWh | 0.04 | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Weekly Total | ||||||
12 | Electric usage | 7/6/20 | 5:00 | 5:59 | 0.12 | kWh | 0.03 | 1/1/21 | 1/2/21 | 1/3/21 | 1/4/21 | 1/5/21 | 1/6/21 | 1/7/21 | |||||||
13 | Electric usage | 7/6/20 | 6:00 | 6:59 | 0.14 | kWh | 0.03 | Usage | 44.32 | 44.81 | 44.04 | 48.05 | 42.17 | 45.15 | 39.03 | 307.57 | kwh | ||||
14 | Electric usage | 7/6/20 | 7:00 | 7:59 | 0.16 | kWh | 0.04 | Cost | $11.05 | $11.18 | $10.99 | $12.02 | $10.53 | $11.68 | $12.29 | $79.74 | Dollars | ||||
15 | Electric usage | 7/6/20 | 8:00 | 8:59 | 0.15 | kWh | 0.04 | ||||||||||||||
16 | Electric usage | 7/6/20 | 9:00 | 9:59 | 0.33 | kWh | 0.08 | ||||||||||||||
17 | Electric usage | 7/6/20 | 10:00 | 10:59 | 0.2 | kWh | 0.05 | Begin Date | 02/01/21 | ||||||||||||
18 | Electric usage | 7/6/20 | 11:00 | 11:59 | 0.24 | kWh | 0.06 | End Date | 02/28/21 | Time | Cost | Usage | |||||||||
19 | Electric usage | 7/6/20 | 12:00 | 12:59 | 0.25 | kWh | 0.06 | 0:00 | kwh | ||||||||||||
20 | Electric usage | 7/6/20 | 13:00 | 13:59 | 0.27 | kWh | 0.07 | 1:00 | kwh | ||||||||||||
21 | Electric usage | 7/6/20 | 14:00 | 14:59 | 0.29 | kWh | 0.07 | 2:00 | kwh | ||||||||||||
22 | Electric usage | 7/6/20 | 15:00 | 15:59 | 0.27 | kWh | 0.07 | 3:00 | kwh | ||||||||||||
23 | Electric usage | 7/6/20 | 16:00 | 16:59 | 0.3 | kWh | 0.07 | 4:00 | kwh | ||||||||||||
24 | Electric usage | 7/6/20 | 17:00 | 17:59 | 0.27 | kWh | 0.07 | 5:00 | kwh | ||||||||||||
25 | Electric usage | 7/6/20 | 18:00 | 18:59 | 0.62 | kWh | 0.15 | 6:00 | kwh | ||||||||||||
26 | Electric usage | 7/6/20 | 19:00 | 19:59 | 0.88 | kWh | 0.22 | 7:00 | kwh | ||||||||||||
27 | Electric usage | 7/6/20 | 20:00 | 20:59 | 1.62 | kWh | 0.39 | 8:00 | kwh | ||||||||||||
28 | Electric usage | 7/6/20 | 21:00 | 21:59 | 1.77 | kWh | 0.43 | 9:00 | kwh | ||||||||||||
29 | Electric usage | 7/6/20 | 22:00 | 22:59 | 1.69 | kWh | 0.41 | 10:00 | kwh | ||||||||||||
30 | Electric usage | 7/6/20 | 23:00 | 23:59 | 0.72 | kWh | 0.17 | 11:00 | kwh | ||||||||||||
31 | Electric usage | 7/7/20 | 0:00 | 0:59 | 0.25 | kWh | 0.06 | 12:00 | kwh | ||||||||||||
32 | Electric usage | 7/7/20 | 1:00 | 1:59 | 0.22 | kWh | 0.05 | 13:00 | kwh | ||||||||||||
33 | Electric usage | 7/7/20 | 2:00 | 2:59 | 0.26 | kWh | 0.06 | 14:00 | kwh | ||||||||||||
34 | Electric usage | 7/7/20 | 3:00 | 3:59 | 0.24 | kWh | 0.06 | 15:00 | kwh | ||||||||||||
35 | Electric usage | 7/7/20 | 4:00 | 4:59 | 0.21 | kWh | 0.05 | 16:00 | kwh | ||||||||||||
36 | Electric usage | 7/7/20 | 5:00 | 5:59 | 0.16 | kWh | 0.04 | 17:00 | kwh | ||||||||||||
37 | Electric usage | 7/7/20 | 6:00 | 6:59 | 0.21 | kWh | 0.05 | 18:00 | kwh | ||||||||||||
38 | Electric usage | 7/7/20 | 7:00 | 7:59 | 0.33 | kWh | 0.08 | 19:00 | kwh | ||||||||||||
39 | Electric usage | 7/7/20 | 8:00 | 8:59 | 0.14 | kWh | 0.03 | 20:00 | kwh | ||||||||||||
40 | Electric usage | 7/7/20 | 9:00 | 9:59 | 0.14 | kWh | 0.03 | 21:00 | kwh | ||||||||||||
41 | Electric usage | 7/7/20 | 10:00 | 10:59 | 0.15 | kWh | 0.04 | 22:00 | kwh | ||||||||||||
42 | Electric usage | 7/7/20 | 11:00 | 11:59 | 0.31 | kWh | 0.08 | 23:00 | kwh | ||||||||||||
43 | Electric usage | 7/7/20 | 12:00 | 12:59 | 0.2 | kWh | 0.05 | ||||||||||||||
44 | Electric usage | 7/7/20 | 13:00 | 13:59 | 0.31 | kWh | 0.07 | 0.00 | |||||||||||||
45 | Electric usage | 7/7/20 | 14:00 | 14:59 | 0.17 | kWh | 0.04 | ||||||||||||||
46 | Electric usage | 7/7/20 | 15:00 | 15:59 | 0.37 | kWh | 0.09 | ||||||||||||||
47 | Electric usage | 7/7/20 | 16:00 | 16:59 | 0.34 | kWh | 0.08 | ||||||||||||||
48 | Electric usage | 7/7/20 | 17:00 | 17:59 | 0.26 | kWh | 0.06 | ||||||||||||||
49 | Electric usage | 7/7/20 | 18:00 | 18:59 | 0.25 | kWh | 0.06 | ||||||||||||||
50 | Electric usage | 7/7/20 | 19:00 | 19:59 | 0.42 | kWh | 0.1 | ||||||||||||||
51 | Electric usage | 7/7/20 | 20:00 | 20:59 | 1 | kWh | 0.24 | ||||||||||||||
52 | Electric usage | 7/7/20 | 21:00 | 21:59 | 2.88 | kWh | 0.7 | ||||||||||||||
53 | Electric usage | 7/7/20 | 22:00 | 22:59 | 1.53 | kWh | 0.37 | ||||||||||||||
54 | Electric usage | 7/7/20 | 23:00 | 23:59 | 0.82 | kWh | 0.2 | ||||||||||||||
PG&E Electricity Use & Cost 07- |
Cell Formulas | ||
---|---|---|
Range | Formula | |
L7:L8 | L7 | =TEXT(K7,"ddd") |
M7:M8 | M7 | =SUMIF(Service_Date,K7,USAGE_Amt) |
N7:N8 | N7 | =SUMIF(Service_Date,K7,Electricity_Cost) |
K11:Q11 | K11 | =TEXT(K12,"ddd") |
L12:Q12 | L12 | =K12+1 |
K13:Q13 | K13 | =SUMIF(Service_Date,K12,USAGE_Amt) |
R13:R14 | R13 | =SUM(K13:Q13) |
K14:Q14 | K14 | =SUMIF(Service_Date,K12,Electricity_Cost) |
M44 | M44 | =(SUM(M19:M42)) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Electricity_Cost | ='PG&E Electricity Use & Cost 07-'!$G$7:$G$10005 | N7:N8, K14:Q14 |
Service_Date | ='PG&E Electricity Use & Cost 07-'!$B$7:$B$10000 | M7:N8, K13:Q14 |
USAGE_Amt | ='PG&E Electricity Use & Cost 07-'!$E$7:$E$10000 | M7:M8, K13:Q13 |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
K7:K8 | List | =Date |
K12 | List | =Date |
J17:J18 | List | =Date |