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
[TABLE="width: 192"]
<tbody>[TR]
[TD]start
[/TD]
[TD]stop
[/TD]
[TD]slot
[/TD]
[/TR]
[TR]
[TD]19:00
[/TD]
[TD]22:00
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

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

F H I
[TABLE="width: 316"]
<tbody>[TR]
[TD]6:00
[/TD]
[TD][/TD]
[TD]6:00-6:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]6:30
[/TD]
[TD][/TD]
[TD]6:30-7:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7:00
[/TD]
[TD][/TD]
[TD]7:00-7:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7:30
[/TD]
[TD][/TD]
[TD]7:30-8:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8:00
[/TD]
[TD][/TD]
[TD]8:00-8:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]8:30
[/TD]
[TD][/TD]
[TD]8:30-9:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]9:00
[/TD]
[TD][/TD]
[TD]9:00-9:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]9:30
[/TD]
[TD][/TD]
[TD]9:30-10:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]10:00
[/TD]
[TD][/TD]
[TD]10:00-10:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]10:30
[/TD]
[TD][/TD]
[TD]10:30-11:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]11:00
[/TD]
[TD][/TD]
[TD]11:00-11:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]11:30
[/TD]
[TD][/TD]
[TD]11:30-12:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]12:00
[/TD]
[TD][/TD]
[TD]12:00-12:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]12:30
[/TD]
[TD][/TD]
[TD]12:30-13:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]13:00
[/TD]
[TD][/TD]
[TD]13:00-13:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]13:30
[/TD]
[TD][/TD]
[TD]13:30-14:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]14:00
[/TD]
[TD][/TD]
[TD]14:00-14:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]14:30
[/TD]
[TD][/TD]
[TD]14:30-15:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]15:00
[/TD]
[TD][/TD]
[TD]15:00-15:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]15:30
[/TD]
[TD][/TD]
[TD]15:30-16:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]16:00
[/TD]
[TD][/TD]
[TD]16:00-16:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]16:30
[/TD]
[TD][/TD]
[TD]16:30-17:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]17:00
[/TD]
[TD][/TD]
[TD]17:00-17:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]17:30
[/TD]
[TD][/TD]
[TD]17:30-18:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]18:00
[/TD]
[TD][/TD]
[TD]18:00-18:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]18:30
[/TD]
[TD][/TD]
[TD]18:30-19:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]19:00
[/TD]
[TD][/TD]
[TD]19:00-19:30
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]19:30
[/TD]
[TD][/TD]
[TD]19:30-20:00
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]20:00
[/TD]
[TD][/TD]
[TD]20:00-20:30
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]20:30
[/TD]
[TD][/TD]
[TD]20:30-21:00
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]21:00
[/TD]
[TD][/TD]
[TD]21:00-21:30
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]21:30
[/TD]
[TD][/TD]
[TD]21:30-22:00
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]22:00
[/TD]
[TD][/TD]
[TD]22:00-22:30
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]22:30
[/TD]
[TD][/TD]
[TD]22:30-23:00
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]23:00
[/TD]
[TD][/TD]
[TD]23:00-
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Re: Sumproduct help

Marcelo Branco said:
Have you tried the formula in post 8 - no need of SUMPRODUCT
I1 copied down
=(F1>=A$2)*(B$2>=F1)*C$2

Worked perfectly for me (see gray area)


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[TD="bgcolor: #DCE6F1"]
I​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Start​
[/TD]
[TD]
Stop​
[/TD]
[TD]
Slot​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
06:00​
[/TD]
[TD][/TD]
[TD]
6:00-6:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
19:00​
[/TD]
[TD]
22:00​
[/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
06:30​
[/TD]
[TD][/TD]
[TD]
6:30-7:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
07:00​
[/TD]
[TD][/TD]
[TD]
7:00-7:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
07:30​
[/TD]
[TD][/TD]
[TD]
7:30-8:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
08:00​
[/TD]
[TD][/TD]
[TD]
8:00-8:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
08:30​
[/TD]
[TD][/TD]
[TD]
8:30-9:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
09:00​
[/TD]
[TD][/TD]
[TD]
9:00-9:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
09:30​
[/TD]
[TD][/TD]
[TD]
9:30-10:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
10:00​
[/TD]
[TD][/TD]
[TD]
10:00-10:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
10:30​
[/TD]
[TD][/TD]
[TD]
10:30-11:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
11:00​
[/TD]
[TD][/TD]
[TD]
11:00-11:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
11:30​
[/TD]
[TD][/TD]
[TD]
11:30-12:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
12:00​
[/TD]
[TD][/TD]
[TD]
12:00-12:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
14​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
12:30​
[/TD]
[TD][/TD]
[TD]
12:30-13:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
13:00​
[/TD]
[TD][/TD]
[TD]
13:00-13:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
16​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
13:30​
[/TD]
[TD][/TD]
[TD]
13:30-14:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
14:00​
[/TD]
[TD][/TD]
[TD]
14:00-14:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
18​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
14:30​
[/TD]
[TD][/TD]
[TD]
14:30-15:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
19​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
15:00​
[/TD]
[TD][/TD]
[TD]
15:00-15:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
20​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
15:30​
[/TD]
[TD][/TD]
[TD]
15:30-16:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
21​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
16:00​
[/TD]
[TD][/TD]
[TD]
16:00-16:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
22​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
16:30​
[/TD]
[TD][/TD]
[TD]
16:30-17:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
23​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
17:00​
[/TD]
[TD][/TD]
[TD]
17:00-17:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
24​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
17:30​
[/TD]
[TD][/TD]
[TD]
17:30-18:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
25​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
18:00​
[/TD]
[TD][/TD]
[TD]
18:00-18:30​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
26​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
18:30​
[/TD]
[TD][/TD]
[TD]
18:30-19:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
27​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
19:00​
[/TD]
[TD][/TD]
[TD]
19:00-19:30​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
28​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
19:30​
[/TD]
[TD][/TD]
[TD]
19:30-20:00​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
29​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
20:00​
[/TD]
[TD][/TD]
[TD]
20:00-20:30​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
30​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
20:30​
[/TD]
[TD][/TD]
[TD]
20:30-21:00​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
31​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
21:00​
[/TD]
[TD][/TD]
[TD]
21:00-21:30​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
32​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
21:30​
[/TD]
[TD][/TD]
[TD]
21:30-22:00​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
33​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
22:00​
[/TD]
[TD][/TD]
[TD]
22:00-22:30​
[/TD]
[TD="bgcolor: #D9D9D9"]
5​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
34​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
22:30​
[/TD]
[TD][/TD]
[TD]
22:30-23:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
35​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
23:00​
[/TD]
[TD][/TD]
[TD]
23:00​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
36​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.
Click to expand...
hey marcelo,

what if i have another row w/ start,stop, slots A3:C3 but C3 is now a text value ("Unlimited") slots . Any work-around for this show "unlimited" from 1030-1500?...currently i'm getting #Value !. thanks for your time.

[TABLE="width: 236"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]start[/TD]
[TD]stop[/TD]
[TD] slots taken[/TD]
[/TR]
[TR]
[TD="align: right"]8:00[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10:30[/TD]
[TD="align: right"]15:00[/TD]
[TD] Unlimited
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Sumproduct help

Try

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

J1 copied down
=IF((F1>=A$3)*(B$3>=F1),C$3,0)

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

Similar threads

C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
113
cooleomter
C
A
  • Solved
Count entry by time
Replies
3
Views
257
admvlad
A
D
  • Solved
VLOOKUP or INDEX/MATCH utilizing day of the week
Replies
5
Views
596
Darren_workforce
D
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
432
dshtesy
D

Forum statistics

Threads
1,224,882
Messages
6,181,545
Members
453,053
Latest member
ezzat

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