sumif...?

morebytes

New Member
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.

Electricity Usage Cost 07-05-2020 to Present.xlsx
ABCDEFGHIJKLMNOPQRS
1Name
3Account Number
4Service
5
6TYPEDATESTART TIMEEND TIMEUSAGEUNITSCOSTNOTESCompareDATEDayElectric UseCostUnit
7Electric usage7/6/200:000:590.37kWh0.091st Day2/1/21Mon35.07\$8.75kwh
8Electric usage7/6/201:001:590.21kWh0.052nd Day2/8/21Mon35.92\$11.28kwh
9Electric usage7/6/202:002:590.16kWh0.04
10Electric usage7/6/203:003:590.17kWh0.04
11Electric usage7/6/204:004:590.15kWh0.04FriSatSunMonTueWedThuWeekly Total
12Electric usage7/6/205:005:590.12kWh0.031/1/211/2/211/3/211/4/211/5/211/6/211/7/21
13Electric usage7/6/206:006:590.14kWh0.03Usage44.3244.8144.0448.0542.1745.1539.03307.57kwh
14Electric usage7/6/207:007:590.16kWh0.04Cost\$11.05\$11.18\$10.99\$12.02\$10.53\$11.68\$12.29\$79.74Dollars
15Electric usage7/6/208:008:590.15kWh0.04
16Electric usage7/6/209:009:590.33kWh0.08
17Electric usage7/6/2010:0010:590.2kWh0.05Begin Date02/01/21
18Electric usage7/6/2011:0011:590.24kWh0.06End Date02/28/21TimeCostUsage
19Electric usage7/6/2012:0012:590.25kWh0.060:00kwh
20Electric usage7/6/2013:0013:590.27kWh0.071:00kwh
21Electric usage7/6/2014:0014:590.29kWh0.072:00kwh
22Electric usage7/6/2015:0015:590.27kWh0.073:00kwh
23Electric usage7/6/2016:0016:590.3kWh0.074:00kwh
24Electric usage7/6/2017:0017:590.27kWh0.075:00kwh
25Electric usage7/6/2018:0018:590.62kWh0.156:00kwh
26Electric usage7/6/2019:0019:590.88kWh0.227:00kwh
27Electric usage7/6/2020:0020:591.62kWh0.398:00kwh
28Electric usage7/6/2021:0021:591.77kWh0.439:00kwh
29Electric usage7/6/2022:0022:591.69kWh0.4110:00kwh
30Electric usage7/6/2023:0023:590.72kWh0.1711:00kwh
31Electric usage7/7/200:000:590.25kWh0.0612:00kwh
32Electric usage7/7/201:001:590.22kWh0.0513:00kwh
33Electric usage7/7/202:002:590.26kWh0.0614:00kwh
34Electric usage7/7/203:003:590.24kWh0.0615:00kwh
35Electric usage7/7/204:004:590.21kWh0.0516:00kwh
36Electric usage7/7/205:005:590.16kWh0.0417:00kwh
37Electric usage7/7/206:006:590.21kWh0.0518:00kwh
38Electric usage7/7/207:007:590.33kWh0.0819:00kwh
39Electric usage7/7/208:008:590.14kWh0.0320:00kwh
40Electric usage7/7/209:009:590.14kWh0.0321:00kwh
41Electric usage7/7/2010:0010:590.15kWh0.0422:00kwh
42Electric usage7/7/2011:0011:590.31kWh0.0823:00kwh
43Electric usage7/7/2012:0012:590.2kWh0.05
44Electric usage7/7/2013:0013:590.31kWh0.070.00
45Electric usage7/7/2014:0014:590.17kWh0.04
46Electric usage7/7/2015:0015:590.37kWh0.09
47Electric usage7/7/2016:0016:590.34kWh0.08
48Electric usage7/7/2017:0017:590.26kWh0.06
49Electric usage7/7/2018:0018:590.25kWh0.06
50Electric usage7/7/2019:0019:590.42kWh0.1
51Electric usage7/7/2020:0020:591kWh0.24
52Electric usage7/7/2021:0021:592.88kWh0.7
53Electric usage7/7/2022:0022:591.53kWh0.37
54Electric usage7/7/2023:0023:590.82kWh0.2
PG&E Electricity Use & Cost 07-
Cell Formulas
RangeFormula
L7:L8L7=TEXT(K7,"ddd")
M7:M8M7=SUMIF(Service_Date,K7,USAGE_Amt)
N7:N8N7=SUMIF(Service_Date,K7,Electricity_Cost)
K11:Q11K11=TEXT(K12,"ddd")
L12:Q12L12=K12+1
K13:Q13K13=SUMIF(Service_Date,K12,USAGE_Amt)
R13:R14R13=SUM(K13:Q13)
K14:Q14K14=SUMIF(Service_Date,K12,Electricity_Cost)
M44M44=(SUM(M19:M42))
Named Ranges
NameRefers ToCells
Electricity_Cost='PG&E Electricity Use & Cost 07-'!\$G\$7:\$G\$10005N7:N8, K14:Q14
Service_Date='PG&E Electricity Use & Cost 07-'!\$B\$7:\$B\$10000M7:N8, K13:Q14
USAGE_Amt='PG&E Electricity Use & Cost 07-'!\$E\$7:\$E\$10000M7:M8, K13:Q13
Cells with Data Validation
CellAllowCriteria
K7:K8List=Date
K12List=Date
J17:J18List=Date

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Anthony47

Well-known Member
Probably:
-in L19 `=SUMIFS(\$G:\$G,\$B:\$B,">="&\$J\$17,\$B:\$B,"<="&\$J\$18,\$C:\$C,\$K19)`
-in M19 `=SUMIFS(\$E:\$E,\$B:\$B,">="&\$J\$17,\$B:\$B,"<="&\$J\$18,\$C:\$C,\$K19)`
Then copy down

Bye

Alex Blakenburg

Well-known Member
See if this works for you.
Copy this down the full range.

@L19 - COST
Excel Formula:
``=SUMIFS(\$G\$7:\$G\$54,\$B\$7:\$B\$54,">="&\$J\$17,\$B\$7:\$B\$54,"<="&\$J\$18,\$C\$7:\$C\$54,">="&K19,\$C\$7:\$C\$54,"<"&K20)``

@M19 - Usage
Excel Formula:
``=SUMIFS(\$E\$7:\$E\$54,\$B\$7:\$B\$54,">="&\$J\$17,\$B\$7:\$B\$54,"<="&\$J\$18,\$C\$7:\$C\$54,">="&K19,\$C\$7:\$C\$54,"<"&K20)``

To make the intervals variable I relied on the entries you had and to calculate it last time slot I had to add 1 additional entry and as follows:-
1/01/1900 12:00:00 AM
It needed the 1/01/1900 to work.
If this doesn't suit you I can think of a couple of alternatives.

You can also get pretty much the same result with a pivot table.

morebytes

New Member
Probably:
-in L19 `=SUMIFS(\$G:\$G,\$B:\$B,">="&\$J\$17,\$B:\$B,"<="&\$J\$18,\$C:\$C,\$K19)`
-in M19 `=SUMIFS(\$E:\$E,\$B:\$B,">="&\$J\$17,\$B:\$B,"<="&\$J\$18,\$C:\$C,\$K19)`
Then copy down

Bye
Anthony...Many, Many Thanks! Cheers!

morebytes

New Member
See if this works for you.
Copy this down the full range.

@L19 - COST
Excel Formula:
``=SUMIFS(\$G\$7:\$G\$54,\$B\$7:\$B\$54,">="&\$J\$17,\$B\$7:\$B\$54,"<="&\$J\$18,\$C\$7:\$C\$54,">="&K19,\$C\$7:\$C\$54,"<"&K20)``

@M19 - Usage
Excel Formula:
``=SUMIFS(\$E\$7:\$E\$54,\$B\$7:\$B\$54,">="&\$J\$17,\$B\$7:\$B\$54,"<="&\$J\$18,\$C\$7:\$C\$54,">="&K19,\$C\$7:\$C\$54,"<"&K20)``

To make the intervals variable I relied on the entries you had and to calculate it last time slot I had to add 1 additional entry and as follows:-
1/01/1900 12:00:00 AM
It needed the 1/01/1900 to work.
If this doesn't suit you I can think of a couple of alternatives.

You can also get pretty much the same result with a pivot table.
Alex, I ABSOLUTELY am grateful for your time and help...Many Thanks! Cheers

Happy to help.

Replies
4
Views
122
Replies
2
Views
59
Replies
4
Views
106
Replies
12
Views
263
Replies
9
Views
171

Forum statistics

1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

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.

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

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