Sumproduct

[bobbysmith]

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>

 

[AhoyNC]

Re: Sumproduct help

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

 

[bobbysmith]

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.

	A	B	C	D	E	F	G	H
1	Start	Stop	Slot			6:00	6:00-6:30	0
2	19:00	22:00	32			6:30	6:30-7:00	0
3			32			7:00	7:00-7:30	0
4						7:30	7:30-8:00	0
5						8:00	8:00-8:30	0
6	Start	Row	27			8:30	8:30-9:00	0
7						9:00	9:00-9:30	0
8						9:30	9:30-10:00	0
9						10:00	10:00-10:30	0
10						10:30	10:30-11:00	0
11						11:00	11:00-11:30	0
12						11:30	11:30-12:00	0
13						12:00	12:00-12:30	0
14						12:30	12:30-13:00	0
15						13:00	13:00-13:30	0
16						13:30	13:30-14:00	0
17						14:00	14:00-14:30	0
18						14:30	14:30-15:00	0
19						15:00	15:00-15:30	0
20						15:30	15:30-16:00	0
21						16:00	16:00-16:30	0
22						16:30	16:30-17:00	0
23						17:00	17:00-17:30	0
24						17:30	17:30-18:00	0
25						18:00	18:00-18:30	0
26						18:30	18:30-19:00	0
27						19:00	19:00-19:30	2
28						19:30	19:30-20:00	5
29						20:00	20:00-20:30	5
30						20:30	20:30-21:00	5
31						21:00	21:00-21:30	5
32						21:30	21:30-22:00	5
33						22:00	22:00-22:30	5
34						22:30	22:30-23:00	0
35						23:00	23:00	0

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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

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

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-

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

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

 

[Marcelo Branco]

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)

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Start​	Stop​	Slot​			06:00​		6:00-6:30​	0​
2​	19:00​	22:00​	5​			06:30​		6:30-7:00​	0​
3​						07:00​		7:00-7:30​	0​
4​						07:30​		7:30-8:00​	0​
5​						08:00​		8:00-8:30​	0​
6​						08:30​		8:30-9:00​	0​
7​						09:00​		9:00-9:30​	0​
8​						09:30​		9:30-10:00​	0​
9​						10:00​		10:00-10:30​	0​
10​						10:30​		10:30-11:00​	0​
11​						11:00​		11:00-11:30​	0​
12​						11:30​		11:30-12:00​	0​
13​						12:00​		12:00-12:30​	0​
14​						12:30​		12:30-13:00​	0​
15​						13:00​		13:00-13:30​	0​
16​						13:30​		13:30-14:00​	0​
17​						14:00​		14:00-14:30​	0​
18​						14:30​		14:30-15:00​	0​
19​						15:00​		15:00-15:30​	0​
20​						15:30​		15:30-16:00​	0​
21​						16:00​		16:00-16:30​	0​
22​						16:30​		16:30-17:00​	0​
23​						17:00​		17:00-17:30​	0​
24​						17:30​		17:30-18:00​	0​
25​						18:00​		18:00-18:30​	0​
26​						18:30​		18:30-19:00​	0​
27​						19:00​		19:00-19:30​	5​
28​						19:30​		19:30-20:00​	5​
29​						20:00​		20:00-20:30​	5​
30​						20:30​		20:30-21:00​	5​
31​						21:00​		21:00-21:30​	5​
32​						21:30​		21:30-22:00​	5​
33​						22:00​		22:00-22:30​	5​
34​						22:30​		22:30-23:00​	0​
35​						23:00​		23:00​	0​
36​

<tbody>
</tbody>

M.

 

[Marcelo Branco]

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.

 

[bobbysmith]

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.

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

 

[Marcelo Branco]

Re: Sumproduct help

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

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

M.

 

[bobbysmith]

Re: Sumproduct help

Hi Marcelo

in an empty cell ......=A2=F27 = FALSE

 

[bobbysmith]

Re: Sumproduct help

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

M.

this is my results after testing each row, the blanks are TRUE:

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

<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>
</tbody>

M.[/QUOTE]

 

[Marcelo Branco]

Re: Sumproduct help

Hi Marcelo

in an empty cell ......=A2=F27 = FALSE

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.

 

[bobbysmith]

Re: Sumproduct help

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.

It work ! but i have to do that every time. Any idea why the its now accepting it as a duplicate value?