Sumproduct

B

bobbysmith

New Member
Joined
Jan 24, 2015
Messages
29
Was hope someone can assist with my formula. I cant understand why results are not showing up on 19:00 and end at 22:00 (Col H is just the intervals). I seems to shift down to start at 19:30 and end at 22:30 instead of starting at 19:00 and ending at 22:00 (start 1900-1930, end 2130-2200). Any insight is greatly appreciated.

Col A Col B Col C
start
stop
slot
19:00
22:00
5

<tbody>
</tbody>

=SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2)

F H I
6:00
6:00-6:30
0
6:30
6:30-7:00
0
7:00
7:00-7:30
0
7:30
7:30-8:00
0
8:00
8:00-8:30
0
8:30
8:30-9:00
0
9:00
9:00-9:30
0
9:30
9:30-10:00
0
10:00
10:00-10:30
0
10:30
10:30-11:00
0
11:00
11:00-11:30
0
11:30
11:30-12:00
0
12:00
12:00-12:30
0
12:30
12:30-13:00
0
13:00
13:00-13:30
0
13:30
13:30-14:00
0
14:00
14:00-14:30
0
14:30
14:30-15:00
0
15:00
15:00-15:30
0
15:30
15:30-16:00
0
16:00
16:00-16:30
0
16:30
16:30-17:00
0
17:00
17:00-17:30
0
17:30
17:30-18:00
0
18:00
18:00-18:30
0
18:30
18:30-19:00
0
19:00
19:00-19:30
0
19:30
19:30-20:00
5
20:00
20:00-20:30
5
20:30
20:30-21:00
5
21:00
21:00-21:30
5
21:30
21:30-22:00
5
22:00
22:00-22:30
5
22:30
22:30-23:00
0
23:00
23:00-
0

<tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Re: Sumproduct help

It works for me. My guess is your formulas are all off one row, shift the whole I column up one row and delete top and fill down one more cell.
 
Upvote 0
Re: Sumproduct help

C Moore said:
It works for me. My guess is your formulas are all off one row, shift the whole I column up one row and delete top and fill down one more cell.
Click to expand...

I've try that and it works but it cause other interval to shift. For example i enter Start 6:00, Stop 7:00, result would only show on 600-630 interval instead of showing on 600-630 and 630-700.
 
Upvote 0
Re: Sumproduct help

Mine still works. Here is what I have
A2: 6:00 B2: 7:00 C2: 5
F1: 6:00 F2: 6:30
I1: =SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2) result: 5
I2: =SUMPRODUCT(($A$2<=F2)*($B$2>F2)*$C$2) result: 5
 
Upvote 0
Re: Sumproduct help

C Moore said:
Mine still works. Here is what I have
A2: 6:00 B2: 7:00 C2: 5
F1: 6:00 F2: 6:30
I1: =SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2) result: 5
I2: =SUMPRODUCT(($A$2<=F2)*($B$2>F2)*$C$2) result: 5
Click to expand...


thank you C Moore, i've duplicated exactly as you have it does work as you have but still no luck when i enter 19:00 start , 22:00 stop. the result still starts at 19:30. Might to search for another resolution. thanks for your time
 
Upvote 0
Re: Sumproduct help

If you want to start at 19:00 and end at 19:00 shouldn't you reverse your less than and greater than signs?
What answer do you expect?
You could also use SUMIFS function depending on ver. of Excel.
Excel Workbook
ABCDEFGHI
1StartStopSlot6:006:00-6:300
219:0022:00306:306:30-7:000
3307:007:00-7:300
47:307:30-8:000
58:008:00-8:300
68:308:30-9:000
79:009:00-9:300
89:309:30-10:000
910:0010:00-10:300
1010:3010:30-11:000
1111:0011:00-11:300
1211:3011:30-12:000
1312:0012:00-12:300
1412:3012:30-13:000
1513:0013:00-13:300
1613:3013:30-14:000
1714:0014:00-14:300
1814:3014:30-15:000
1915:0015:00-15:300
2015:3015:30-16:000
2116:0016:00-16:300
2216:3016:30-17:000
2317:0017:00-17:300
2417:3017:30-18:000
2518:0018:00-18:300
2618:3018:30-19:000
2719:0019:00-19:300
2819:3019:30-20:005
2920:0020:00-20:305
3020:3020:30-21:005
3121:0021:00-21:305
3221:3021:30-22:005
3322:0022:00-22:305
3422:3022:30-23:000
3523:0023:000
Sheet
 
Upvote 0
Re: Sumproduct help

AhoyNC said:
If you want to start at 19:00 and end at 19:00 shouldn't you reverse your less than and greater than signs?
What answer do you expect?
You could also use SUMIFS function depending on ver. of Excel.

ABCDEFGHI
1StartStopSlot6:006:00-6:300
219:0022:00306:306:30-7:000
3307:007:00-7:300
47:307:30-8:000
58:008:00-8:300
68:308:30-9:000
79:009:00-9:300
89:309:30-10:000
910:0010:00-10:300
1010:3010:30-11:000
1111:0011:00-11:300
1211:3011:30-12:000
1312:0012:00-12:300
1412:3012:30-13:000
1513:0013:00-13:300
1613:3013:30-14:000
1714:0014:00-14:300
1814:3014:30-15:000
1915:0015:00-15:300
2015:3015:30-16:000
2116:0016:00-16:300
2216:3016:30-17:000
2317:0017:00-17:300
2417:3017:30-18:000
2518:0018:00-18:300
2618:3018:30-19:000
2719:0019:00-19:300
2819:3019:30-20:005
2920:0020:00-20:305
3020:3020:30-21:005
3121:0021:00-21:305
3221:3021:30-22:005
3322:0022:00-22:305
3422:3022:30-23:000
3523:0023:000

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C2=SUMIFS($I$1:$I$35,$F$1:$F$35,">="&$A$2,$F$1:$F$35,"<="&$B$2)
C3=SUMPRODUCT(($F$1:$F$35>=$A$2)*($F$1:$F$35<=$B$2),$I$1:$I$35)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
(19
Thank you for your time AhoyNC....the results i have is exactly like your's where the Result starts at Row 28 (19:30-20:00) and ends at Row 33 (22:00-22:30). I need the Results to Start at Row 27 (19:00-19:30) and ends at Row32 (21:30-22:00). If i entered Values between 6:00 Start thru 1230 Stop it works perfectly. After 1230 the Results shift down one interval.
 
Last edited:
Upvote 0
Re: Sumproduct help

Maybe...

I1 copied down
=(F1>=A$2)*(B$2>=F1)*C$2

Hope this helps

M.
 
Upvote 0
Re: Sumproduct help

Not sure I understand. When I check it starts on row 27 (see below). When I put a number in cell H27 the formula picked it up.
Excel Workbook
ABCDEFGH
1StartStopSlot6:006:00-6:300
219:0022:00326:306:30-7:000
3327:007:00-7:300
47:307:30-8:000
58:008:00-8:300
6StartRow278:308:30-9:000
79:009:00-9:300
89:309:30-10:000
910:0010:00-10:300
1010:3010:30-11:000
1111:0011:00-11:300
1211:3011:30-12:000
1312:0012:00-12:300
1412:3012:30-13:000
1513:0013:00-13:300
1613:3013:30-14:000
1714:0014:00-14:300
1814:3014:30-15:000
1915:0015:00-15:300
2015:3015:30-16:000
2116:0016:00-16:300
2216:3016:30-17:000
2317:0017:00-17:300
2417:3017:30-18:000
2518:0018:00-18:300
2618:3018:30-19:000
2719:0019:00-19:302
2819:3019:30-20:005
2920:0020:00-20:305
3020:3020:30-21:005
3121:0021:00-21:305
3221:3021:30-22:005
3322:0022:00-22:305
3422:3022:30-23:000
3523:0023:000
Sheet
 
Upvote 0
Re: Sumproduct help

AhoyNC

I think the formulas should be placed in column I. A2, B2 and C2 are given.
See post 8

M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Help with rank and difference.
Replies
3
Views
58
Fluff
Fluff
A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
88
Aadya
A
F
  • Question
Excel Formula for Dynamic Timetable with Variable Time Increments based on certain hours of the day
Replies
1
Views
325
Muhammad_Usman
Muhammad_Usman
C
  • Solved
Convert and copy to Date
Replies
5
Views
109
Micron
M
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw

Forum statistics

Threads
1,214,908
Messages
6,122,187
Members
449,071
Latest member
cdnMech

Share this page

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
Back
Top