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

 

[C Moore]

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.

 

[bobbysmith]

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.

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.

 

[C Moore]

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

 

[bobbysmith]

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

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

 

[AhoyNC]

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
	A	B	C	D	E	F	G	H	I
1	Start	Stop	Slot			6:00		6:00-6:30	0
2	19:00	22:00	30			6:30		6:30-7:00	0
3			30			7:00		7:00-7:30	0
4						7:30		7:30-8:00	0
5						8:00		8:00-8:30	0
6						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	0
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
Sheet

 

[bobbysmith]

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.

	A	B	C	D	E	F	G	H	I
			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"]30[/TD]

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

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

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

[TD="align: center"]30[/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"]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"]0[/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

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

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

 

[Marcelo Branco]

Re: Sumproduct help

Maybe...

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

Hope this helps

M.

 

[AhoyNC]

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

 

[Marcelo Branco]

Re: Sumproduct help

AhoyNC

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

M.