sumif...?

morebytes

New Member
Joined
Mar 2, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
2Address
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,148
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
Joined
Feb 23, 2021
Messages
805
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 2, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 2, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,802
Messages
5,655,379
Members
418,193
Latest member
RichKatzRich

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
Top