Item Contract Delivery Planned Delivery
a 2008-47 2008-48
b 2008-48 2008-48
c 2008-47 2008-47
b 2008-49 2008-50
a 2008-50 2008-50
b 2008-47 2008-50
c 2008-47 2008-47
b 2008-51 2008-51
a 2008-51 2008-51
a 2008-46 2008-49
c 2008-48 2008-48
b 2008-49 2008-51
a 2008-49 2008-49
c 2008-50 2008-50

Week Total Delayed per week
2008-46 Formula???
2008-47 Formula???
2008-48 Formula???
2008-49 Formula???
2008-50 Formula???
2008-51 Formula???

I've got this information. What formula can I use to display how many items
are delayed per week? I intend to use this information to make an overview
over delayed items and the progress on the backlog.

Hope anyone understand my question and can help me.

Kriss,

from your posted example what answer do you expect to get for this week?

2008-49 Answer?


Mike

"Kriss" wrote:

Item Contract Delivery Planned Delivery
a 2008-47 2008-48
b 2008-48 2008-48
c 2008-47 2008-47
b 2008-49 2008-50
a 2008-50 2008-50
b 2008-47 2008-50
c 2008-47 2008-47
b 2008-51 2008-51
a 2008-51 2008-51
a 2008-46 2008-49
c 2008-48 2008-48
b 2008-49 2008-51
a 2008-49 2008-49
c 2008-50 2008-50

Week Total Delayed per week
2008-46 Formula???
2008-47 Formula???
2008-48 Formula???
2008-49 Formula???
2008-50 Formula???
2008-51 Formula???

I've got this information. What formula can I use to display how many items
are delayed per week? I intend to use this information to make an overview
over delayed items and the progress on the backlog.

Hope anyone understand my question and can help me.

My hope is to get an answer that tells me how many items are delayed this week.
Meaning how many are supposed to be delivered but are not.

2008-49 4

These items:
b 2008-49 2008-50
b 2008-47 2008-50
a 2008-46 2008-49
b 2008-49 2008-51

"Mike H" wrote:

Kriss,

from your posted example what answer do you expect to get for this week?

2008-49 Answer?


Mike

"Kriss" wrote:

Item Contract Delivery Planned Delivery
a 2008-47 2008-48
b 2008-48 2008-48
c 2008-47 2008-47
b 2008-49 2008-50
a 2008-50 2008-50
b 2008-47 2008-50
c 2008-47 2008-47
b 2008-51 2008-51
a 2008-51 2008-51
a 2008-46 2008-49
c 2008-48 2008-48
b 2008-49 2008-51
a 2008-49 2008-49
c 2008-50 2008-50

Week Total Delayed per week
2008-46 Formula???
2008-47 Formula???
2008-48 Formula???
2008-49 Formula???
2008-50 Formula???
2008-51 Formula???

I've got this information. What formula can I use to display how many items
are delayed per week? I intend to use this information to make an overview
over delayed items and the progress on the backlog.

Hope anyone understand my question and can help me.

please try this

=IF(COUNTIFS($B$2:$B$15,A17)=COUNTIFS($B$2:$B$15, A17,$C$2:$C$15,A17),COUNTIFS($B$2:$B$15,A17)-COUNTIFS($B$2:$B$15,A17,$C$2:$C$15,A17),COUNTIFS($ B$2:$B$15,A17,$C$2:$C$15,A17))


the values for
my assumptions:

item, contract delivery planned delivery are entered till row 15th and
weeks like 2008-46 and more are entred in cell a17 and below.

--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Kriss" wrote:

My hope is to get an answer that tells me how many items are delayed this week.
Meaning how many are supposed to be delivered but are not.

2008-49 4

These items:
b 2008-49 2008-50
b 2008-47 2008-50
a 2008-46 2008-49
b 2008-49 2008-51

"Mike H" wrote:

Kriss,

from your posted example what answer do you expect to get for this week?

2008-49 Answer?


Mike

"Kriss" wrote:

Item Contract Delivery Planned Delivery
a 2008-47 2008-48
b 2008-48 2008-48
c 2008-47 2008-47
b 2008-49 2008-50
a 2008-50 2008-50
b 2008-47 2008-50
c 2008-47 2008-47
b 2008-51 2008-51
a 2008-51 2008-51
a 2008-46 2008-49
c 2008-48 2008-48
b 2008-49 2008-51
a 2008-49 2008-49
c 2008-50 2008-50

Week Total Delayed per week
2008-46 Formula???
2008-47 Formula???
2008-48 Formula???
2008-49 Formula???
2008-50 Formula???
2008-51 Formula???

I've got this information. What formula can I use to display how many items
are delayed per week? I intend to use this information to make an overview
over delayed items and the progress on the backlog.

Hope anyone understand my question and can help me.

I believe this formula will work for you, and it will also work in xl2003
(the countifs function is xl2007).
=SUMPRODUCT(--(LEFT($B$2:$B$15,4)&RIGHT($B$2:$B$15,2)<=LEFT(E17, 4)&RIGHT(E17,4)),--(LEFT($C$2:$C$15,4)&RIGHT($C$2:$C$15,2)=LEFT(E17, 4)&RIGHT(E17,2)),--(LEFT($C$2:$C$15,4)&RIGHT($C$2:$C$15,2)LEFT($B$2: $B$15,4)&RIGHT($B$2:$B$15,2)))

Obviously, expand the range as needed.

--
** John C **

"Kriss" wrote:

My hope is to get an answer that tells me how many items are delayed this week.
Meaning how many are supposed to be delivered but are not.

2008-49 4

These items:
b 2008-49 2008-50
b 2008-47 2008-50
a 2008-46 2008-49
b 2008-49 2008-51

"Mike H" wrote:

Kriss,

from your posted example what answer do you expect to get for this week?

2008-49 Answer?


Mike

"Kriss" wrote:

Item Contract Delivery Planned Delivery
a 2008-47 2008-48
b 2008-48 2008-48
c 2008-47 2008-47
b 2008-49 2008-50
a 2008-50 2008-50
b 2008-47 2008-50
c 2008-47 2008-47
b 2008-51 2008-51
a 2008-51 2008-51
a 2008-46 2008-49
c 2008-48 2008-48
b 2008-49 2008-51
a 2008-49 2008-49
c 2008-50 2008-50

Week Total Delayed per week
2008-46 Formula???
2008-47 Formula???
2008-48 Formula???
2008-49 Formula???
2008-50 Formula???
2008-51 Formula???

I've got this information. What formula can I use to display how many items
are delayed per week? I intend to use this information to make an overview
over delayed items and the progress on the backlog.

Hope anyone understand my question and can help me.