Formula for displaying Total Overdue entries.

**noahsampeer** · 12-15-2010, 02:29 PM

Hi, I am kind of new at Excel and everything I've done so far has been trial and error. Emphasis on the error portion of that last sentence. Anyways, I've got everything that I want done, minus this one formula. I have 2 columns, one is a due date and another is a completed by date. The due dates will automatically format red if it goes overdue and stay that way until someone enters text into the completed column. The due date then removes all of its formating. I have a 3rd column for total overdue. In that column, I have input the formula: =COUNTIF(A1:A20,"<"TODAY()). This formula counts all the overdue dates regardless of the completion status. I was wondering if there was a way to have that COUNTIF statement subtract the completed events by using something along the lines of =IFTEXT(B1:B20)= True, subtract one from Overdue Total?

1 Due Date - Completed - Total overdue
2 Dec 1, 2010 - Nov. 30 - *Don't count this one
3 Dec 5, 2010 - *count this one

I hope that helps. Thanks for any help you can give.

Jason

**DonkeyOte** · 12-16-2010, 03:38 AM

Given use of XL2007 you could try using COUNTIFS:

=COUNTIFS(A:A,"<"&TODAY(),B:B,"")
modify ranges as appropriate

**noahsampeer** · 12-16-2010, 10:42 AM

I had something similar to that but that just gives me a date. More specifically 1 Jan 1900.

Again, I need it to count how many entries are overdue (A) and then look at the Completed column and count the number of cells with text (B) and then do A-B=C.

C is the number that I need displayed.

**DonkeyOte** · 12-16-2010, 11:52 AM

Format the cell containing the COUNTIFS function as General/Number and you will see 1 rather than 1 Jan 1900.

Dates in Excel are just Serial Numbers - 1 Jan 1900 being 1 - it is the format you apply to the cell that determines how that "number" is being presented (ie as a Date or as a Number/Decimal etc)

On a final note - please do not quote prior posts in their entirety - this clutters your thread and the board in general.

Thanks,