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
Re: Sumproduct help

Marcelo,
I think you are right. I misread the question. Your answer looks good to me.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Re: Sumproduct help

AhoyNC said:
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.

ABCDEFGH
6:00-6:30
6:30-7:00
7:00-7:30
7:30-8:00
8:00-8:30
8:30-9:00
9:00-9:30
9:30-10:00
10:00-10:30
10:30-11:00
11:00-11:30
11:30-12:00
12:00-12:30
12:30-13:00
13:00-13:30
13:30-14:00
14:00-14:30
14:30-15:00
15:00-15:30
15:30-16:00
16:00-16:30
16:30-17:00
17:00-17:30
17:30-18:00
18:00-18:30
18:30-19:00
19:00-19:30
19:30-20:00
20:00-20:30
20:30-21:00
21:00-21:30
21:30-22:00
22:00-22:30
22:30-23:00

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]Stop[/TD]
[TD="align: center"]Slot[/TD]

[TD="align: right"]6:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]19:00[/TD]
[TD="align: center"]22:00[/TD]
[TD="align: center"]32[/TD]

[TD="align: right"]6:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]7:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]7:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Start[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]27[/TD]

[TD="align: right"]8:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]11:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]11:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]12:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]12:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]13:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]13:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]14:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]14:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]15:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]15:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]16:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]16:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]17:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]17:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]18:00[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]18:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]19:00[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]19:30[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]20:00[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]20:30[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]21:00[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]21:30[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]22:00[/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]22:30[/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]23:00[/TD]
[TD="align: right"]23:00[/TD]
[TD="align: right"]0[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=SUMIFS($H$1:$H$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),$H$1:$H$35)
C6{=MIN(IF($F$1:$F$35>=$A$2,ROW($F$1:$F$35)-ROW($F$1)+1))}

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...

AHoyNC, Marcelo is correct A,B & C are given and formula should in Column H. The User enters A,B, & C. I need the Value in C to be copied to down the given Start and Stop based on entries in A & B. So if "C" was 5 slots, then Value 5 needs to start at Row 27 (1900-1930) and end (2130-2200) at Row 32 but for some reason its shifting down one Row. Again thank you and Marcelo's time.

I1 =SUMPRODUCT(($A$2<=F1)*($B$2>F1)*$C$2)
F I
[TABLE="width: 351"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: right"]6:00[/TD]
[TD][/TD]
[TD]6:00-6:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6:30[/TD]
[TD][/TD]
[TD]6:30-7:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7:00[/TD]
[TD][/TD]
[TD]7:00-7:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7:30[/TD]
[TD][/TD]
[TD]7:30-8:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8:00[/TD]
[TD][/TD]
[TD]8:00-8:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8:30[/TD]
[TD][/TD]
[TD]8:30-9:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9:00[/TD]
[TD][/TD]
[TD]9:00-9:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9:30[/TD]
[TD][/TD]
[TD]9:30-10:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10:00[/TD]
[TD][/TD]
[TD]10:00-10:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10:30[/TD]
[TD][/TD]
[TD]10:30-11:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11:00[/TD]
[TD][/TD]
[TD]11:00-11:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]11:30[/TD]
[TD][/TD]
[TD]11:30-12:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12:00[/TD]
[TD][/TD]
[TD]12:00-12:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]12:30[/TD]
[TD][/TD]
[TD]12:30-13:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13:00[/TD]
[TD][/TD]
[TD]13:00-13:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]13:30[/TD]
[TD][/TD]
[TD]13:30-14:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14:00[/TD]
[TD][/TD]
[TD]14:00-14:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14:30[/TD]
[TD][/TD]
[TD]14:30-15:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15:00[/TD]
[TD][/TD]
[TD]15:00-15:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15:30[/TD]
[TD][/TD]
[TD]15:30-16:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]16:00[/TD]
[TD][/TD]
[TD]16:00-16:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]16:30[/TD]
[TD][/TD]
[TD]16:30-17:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]17:00[/TD]
[TD][/TD]
[TD]17:00-17:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]17:30[/TD]
[TD][/TD]
[TD]17:30-18:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18:00[/TD]
[TD][/TD]
[TD]18:00-18:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]18:30[/TD]
[TD][/TD]
[TD]18:30-19:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]19:00[/TD]
[TD][/TD]
[TD]19:00-19:30[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]19:30[/TD]
[TD][/TD]
[TD]19:30-20:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20:00[/TD]
[TD][/TD]
[TD]20:00-20:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20:30[/TD]
[TD][/TD]
[TD]20:30-21:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]21:00[/TD]
[TD][/TD]
[TD]21:00-21:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]21:30[/TD]
[TD][/TD]
[TD]21:30-22:00[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]22:00[/TD]
[TD][/TD]
[TD]22:00-22:30[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]22:30[/TD]
[TD][/TD]
[TD]22:30-23:00[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]23:00[/TD]
[TD][/TD]
[TD]23:00-[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

<col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 40px;"><col style="width: 41px;"><col style="width: 85px;"><col style="width: 90px;"><col style="width: 64px;"></colgroup><tbody> </tbody>
 
Last edited:
Upvote 0
Re: Sumproduct help

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.
 
Upvote 0
Re: Sumproduct help

If the formula above didn't work for you, check the value in A2
Click in A2
Take a look at the Formula Bar - you should see
19:00:00
not
19:00:01 or 19:00:02

M.
 
Upvote 0
Re: Sumproduct help

Marcelo Branco said:
If the formula above didn't work for you, check the value in A2
Click in A2
Take a look at the Formula Bar - you should see
19:00:00
not
19:00:01 or 19:00:02

M.
Click to expand...

I basically duplicated exactly as yours and still didnt work. I also checked the formula bar for A2 it shows 7:00:00 PM. No 01 or 02. It must be something with my excel settings maybe? using office pro 2010
 
Upvote 0
Re: Sumproduct help

bobbysmith said:
I basically duplicated exactly as yours and still didnt work. I also checked the formula bar for A2 it shows 7:00:00 PM. No 01 or 02. It must be something with my excel settings maybe? using office pro 2010
Click to expand...


I'm also using Excel 2010 pro.
Last check
Try in an empty cell
=A2=F27
tell us the result

M.
 
Upvote 0
Re: Sumproduct help

Marcelo Branco said:
I'm also using Excel 2010 pro.
Last check
Try in an empty cell
=A2=F27
tell us the result

M.
Click to expand...
this is my results after testing each row, the blanks are TRUE:
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"]6:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]6:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]7:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]7:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]8:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]9:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]9:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]10:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]10:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]11:00[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]11:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]12:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]12:30[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]13:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]13:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]14:00[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]14:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]15:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]15:30[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]16:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]16:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]17:00[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]17:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]18:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]18:30[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]19:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]20:00[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]20:30[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]21:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]21:30[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]22:00[/TD]
[/TR]
[TR]
[TD="align: center"]FALSE[/TD]
[TD="class: xl63, align: right"]22:30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]23:00[/TD]
[/TR]
</tbody>[/TABLE]

M.[/QUOTE]
 
Upvote 0
Re: Sumproduct help

bobbysmith said:
Hi Marcelo

in an empty cell ......=A2=F27 = FALSE
Click to expand...

Wow... we found the culprit!
Shouldn't 19:00 (A2) be equal to 19:00 (F27)?
Type 19:00 in F27 and see if the formula works.

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