Vertical search? several conditions... problem

[Seeboo]

Hello,

I need some help... In worksheet "Table" I need formula for B & E columns. I need to know which "Part No" was produce in some day between ex. 01:00 - 02:00 and how much. I know that sometimes there will be two or more different Part No. in column B (in this case should be error #N/D! - there is no problem I will ignore that).

2633d28ea66c43ebgen.jpg

In the attachment file.

[Richard Buttrey]

Hi,

I fear you're not going to get very far with this until you split the column V times from the dates.
Once you have proper date and time numbers in individual columns then it's relatively easy to analyse.

[JohnTopley]

Try this in B3

=IFERROR(INDEX(Data!$F$2:$F$14000,SMALL(IF((Data!$M$2:$M$14000=Table!$A3)*(INT(Data!$V$2:$V$14000)=INT(Table!$C3))*(MOD(Data!$V$2:$V$14000,1)>=TIMEVALUE(LEFT(Table!D3,5)))*(MOD(Data!$V$2:$V$14000,1)<=TIMEVALUE(RIGHT(Table!D3,5))),ROW(Data!$F$2:$F$14000)-ROW($F$2)+1,""),1)),"")

Enter with Ctrl+Shift+Enter

Assumes only ONE part

[Seeboo]

Hello Richard,

In the attachment corrected file (seperated date and time.

JohnTopley,
It does not working. I copied formula then used ctrl+alt+entere and returned error.

Regards,

[FDibbins]

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue Dec 2016) is actually 42724

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

So, having said that, as soon as you combine 2 times to for a range, like you have in your 2nd file (00:00 - 01:00), you have created a text cell, it is no longer time

It looks like you are dealing with 1-hour brackets, so why not just use the 1st hour?

[JohnTopley]

This is my original ...

[Seeboo]

Hello JohnTopley,

But there is empty Q'ty (column E)...

[JohnTopley]

This in column E

in E3

=IFERROR(INDEX(Data!$N$2:$N$14000,SMALL(IF((Data!$M$2:$M$14000=Table!$A3)*(INT(Data!$V$2:$V$14000)=INT(Table!$C3))*(MOD(Data!$V$2:$V$14000,1)>=TIMEVALUE(LEFT(Table!$D3,5)))*(MOD(Data!$V$2:$V$14000,1)<=TIMEVALUE(RIGHT(Table!$D3,5))),ROW(Data!$F$2:$F$14000)-ROW($F$2)+1,""),1)),"")

Enter with Ctrl+Shift+Enter

Copy down

[Seeboo]

Working but I need sum during that period (ex. 6:00 - 7:00)

[AliGW]

Seeboo - please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below, or edit out unnecessary content before posting. Thanks.

[JohnTopley]

Qty

=SUMPRODUCT((Data!$N$2:$N$14000)*(Data!$M$2:$M$14000=Table!$A3)*(INT(Data!$V$2:$V$14000)=INT(Table!$C3))*(MOD(Data!$V$2:$V$14000,1)>=TIMEVALUE(LEFT(Table!$D3,5)))*(MOD(Data!$V$2:$V$14000,1)<=TIMEVALUE(RIGHT(Table!$D3,5))))

[JohnTopley]

Using your updated file:

in B3

=IFERROR(INDEX(Data!$F$2:$F$14000,SMALL(IF((Data!$M$2:$M$14000=Table!$A3)*(Data!$V$2:$V$14000=Table!$C3)*(Data!$W$2:$W$14000 >= TIMEVALUE(LEFT(Table!$D3,5)))*(Data!$W$2:$W$14000 <= TIMEVALUE(RIGHT(Table!$D3,5))),ROW(Data!$F$2:$F$14000)-ROW($F$2)+1,""),1)),"")

Enter with Ctrl+Shift+Enter

in E3

=SUMPRODUCT((Data!$N$2:$N$14000)*(Data!$F$2:$F$14000=$B3)*(Data!$V$2:$V$14000=$C3)*(Data!$W$2:$W$14000 >= TIMEVALUE(LEFT($D3,5)))*(Data!$W$2:$W$14000 < TIMEVALUE(RIGHT($D3,5))))