Calculating a Missed Deadline

dlj2007

New Member
Joined
Nov 11, 2013
Messages
30
I need to know how to count how many rows have missed a deadline. So I have 800 rows. Each row contains a Due Date(Column F) and a Date Sent (Column AH). I need to know the count of how many of those rows have missed a deadline. Is this even possible to do without having to create some type of separate column?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try COUNTIF:
Excel Workbook
CDEFGAGAHAI
1LateDueSent
2311/5/201311/5/2013
310/20/201310/22/2013
410/22/201311/1/2013
511/4/201311/3/2013
611/8/201311/9/2013
7
Sheet
 
Upvote 0
Try COUNTIF:

*CDEFGAGAHAI
1*Late*Due**Sent*
2*3*11/5/2013**11/5/2013*
3***10/20/2013**10/22/2013*
4***10/22/2013**11/1/2013*
5***11/4/2013**11/3/2013*
6***11/8/2013**11/9/2013*
7********

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 75px;"><col style="width: 56px;"><col style="width: 40px;"><col style="width: 75px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D2=COUNTIF($F$2:$F$6,"<"&AH2:AH6)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

I tried putting this in and it only returns a 0. So I put the exact same values above into an actual spreadsheet and it still does the same, return a 0. Any other options for me?
 
Upvote 0
I tried putting this in and it only returns a 0. So I put the exact same values above into an actual spreadsheet and it still does the same, return a 0. Any other options for me?

Works for me. Are you sure you got his data in columns F and AH?
 
Upvote 0
Not sure it worked for me.
Did you put the data in the same columns. I hide a number of columns in the example? As per your post the Dates are in columns F and AH.
What ver. of Excel do you have COUNTIF is in Excel 2007 and later.

See if SUMPRODUCT will work:
Excel Workbook
CDEFGAGAHAI
1DueSent
2311/5/1311/5/13
310/20/1310/22/13
410/22/1311/1/13
511/4/1311/3/13
611/8/1311/9/13
7
Sheet
 
Upvote 0
Not sure it worked for me.
Did you put the data in the same columns. I hide a number of columns in the example? As per your post the Dates are in columns F and AH.
What ver. of Excel do you have COUNTIF is in Excel 2007 and later.

See if SUMPRODUCT will work:

*CDEFGAGAHAI
1***Due**Sent*
2*3*11/5/13**11/5/13*
3***10/20/13**10/22/13*
4***10/22/13**11/1/13*
5***11/4/13**11/3/13*
6***11/8/13**11/9/13*
7********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:75px;"><col style="width:64px;"><col style="width:64px;"><col style="width:82px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
D2=SUMPRODUCT(--($F$2:$F$6<$AH$2:$AH$6))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Yea sum product worked. I have 2010. Now can I embed an IF statement In this? I'm seeing I may have to consider the first column as well. So it would be to calculate that sum product if there are dates listed in column A.
 
Upvote 0
Can you post a small sample of what you want. Do you have dates in 3 different columns (A, F and AH)?
In 2010 there is also a COUNTIFS function that may do what you want.
 
Upvote 0
Column A
ADR Due
ADR Sent
11/01/2013
11/3/2013
11/03/2013
10/30/2013
10/31/2013
10/22/2013
11/01/2013
11/05/2013
11/07/2013
12/01/13
12/01/2013
12/10/2013
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<tbody> </tbody>
The sum product works just fine; however my totals aren't coming up right because I need to consider another column. The thing is, people will be adding to this daily. So I have to expand my range to about the 800th row, even though we only have the first 100 filled in for now. So my thoughts were to possibly say, 'count how many ADR's sent met the deadline/due date, but only count the ones that have data entered into column A.
 
Upvote 0
We can disregard my above question, because I figured out I had the incorrect sheet name attached. So all is working with the sumproduct formula in there. It gives me the correct count of those rows that have missed their deadline. But in regard to this, if I wanted to know within a certain time frame, how would I add to this formula. On my summary sheet, I have this sumproduct calculation for how many missed the deadline. But now I want to know, how do I put in for it to calculate the number missed within the last 14 days and within the last 30 days. I entered =SUMPRODUCT(--Sheet1!AH2:AH800>Sheet1!D2:D800)) [WHICH IS RIGHT]; I added to the back of that "&Today() -14.....but it didn't work.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top