formula for missed duew date by operator

[kumar1803]

Hello

I need help with a formula where I would like to count how many items have missed due date by operator. Here is my excel sheet look like:

[TABLE="width: 456"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD] Item
[/TD]
[TD] Description of action item
[/TD]
[TD]Operator
[/TD]
[TD] Due By
[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2015[/TD]
[TD="align: right"]1[/TD]
[TD] Team Not Active
[/TD]
[TD]Manikant[/TD]
[TD="align: right"]20-Apr[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Mike LH]

Hi,

How do we tell from that whether an item has been completed, all it seems to tell us is that the item was due on 20 Apr?

 

[kumar1803]

Sorry I missed the status column . It is after due date. We are marking them complete if completed prior to due date.

 

[Mike LH]

Hi,

So let's say that when completed you enter completed in Col F. you can use this where G1 is the name you want to count.


=SUMPRODUCT((D2:D20=G1)*(F2:F20<>"Completed")*(E2:E20<TODAY()))

 

[kumar1803]

I tried the above formula. I got "0" even for in progress. Keep in mind I would to like to see only those that are missed due date and still in progress.

[TABLE="width: 510"]
<colgroup><col><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]Operator[/TD]
[TD]Due by [/TD]
[TD]Status[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD="align: right"]3/25/2015[/TD]
[TD="align: right"]1[/TD]
[TD]abc[/TD]
[TD]Man[/TD]
[TD="align: right"]4/15/2015[/TD]
[TD]Completed[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4/15/2015[/TD]
[TD="align: right"]2[/TD]
[TD]xyz[/TD]
[TD]SHE[/TD]
[TD="align: right"]4/19/2015[/TD]
[TD]Inprogress[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3/15/2015[/TD]
[TD="align: right"]3[/TD]
[TD]ttt[/TD]
[TD]hes[/TD]
[TD="align: right"]4/5/2015[/TD]
[TD]Inprogress[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[MARK858]

@MikeLH

Hi Mike, the board interprets the < symbol as a Html tag and so to get it to display correctly put a space after the < and ask the OP to remove the space.

=SUMPRODUCT((D2:D20=G1)*(F2:F20<>"Completed")*(E2:E20< TODAY()))

@kumar1803 You need another column/cell to lookup the operator names

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Item	Description	Operator	Due by	Status	Operator LU	formula
2	3/25/2015	1	abc	Man	15/04/2015	Completed	hes	1
3	4/15/2015	2	xyz	SHE	19/04/2015	Inprogress	SHE	1
4	3/15/2015	3	ttt	hes	05/04/2015	Inprogress	Man	0
Sheet1

 

[kumar1803]

Hi Mark

Thank you for quick response. In the example above, I see you have counted both "Completed" and :InProgress" . I would like to count only those " InProgress"

 

[MARK858]

Re-read it. It references the names in column G only as the Operator name to look up.
Man and SHE in Column D are both incomplete in column F and so have a count of 1. Man in column D is Completed in column F and so is 0.

 

[Mike LH]

@MikeLH

Hi Mike, the board interprets the < symbol as a Html tag and so to get it to display correctly put a space after the < and ask the OP to remove the space.



@kumar1803 You need another column/cell to lookup the operator names

Excel Workbook
	A	B	C	D	E	F	G	H
1	Date	Item	Description	Operator	Due by	Status	Operator LU	formula
2	3/25/2015	1	abc	Man	15/04/2015	Completed	hes	1
3	4/15/2015	2	xyz	SHE	19/04/2015	Inprogress	SHE	1
4	3/15/2015	3	ttt	hes	05/04/2015	Inprogress	Man	0
Sheet1

Hmmm,

Well I just learnt something thanks.