Data Sheet
Data Sheet  

I have a reporting template I am creating and have a question for you. On my 'report sheet' I have 4 metrics I am calculating regarding overdue problem report fix responsiveness. The first is the count of 'Major Overdue Closed' problem reports in the report month, second is 'Major Overdue' problem reports in the report month, the third is 'Minor Overdue Closed' problem reports in the report month, and lastly is 'Minor Overdue' problem reports in the report month. I need some help with the 'Minor Overdue' and 'Major Overdue' formulas. Right now the formula for Major Overdue is:
=COUNTIFS(Fix_Due_Date,">="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures),1),Fix_Due_Date,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Major",TL_CategoryNPR, " Enclosures")
And the formula for Minor Overdue is:
=COUNTIFS(Fix_Due_Date,">="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures),1),Fix_Due_Date,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Minor",TL_CategoryNPR, " Enclosures")
I have attached a screen shot of where those formulas are linked from.
('Report_MonthEnclosures' is a cell on the report sheet, this cell is a data validation list that someone chooses the month and year from which they are reporting for. )
Right now, the formulas are working well...  When I select July- 13 for my Report_MonthEnclosures cell for the report month, the metrics seem to calculate correctly. In this example I sent you via screen shot there are 4 Major Overdues and 4 Minor Overdues in the month of July. 2 of those 4 Major Overdues were closed in and 3 of the 4 Minor Overdues were closed.
My only problem now is to get it so that in my COUNTIFS formulas shows that the major and minor overdues are overdue in every month until they are closed.
For example, in the first screenshot I sent you, the blank highlighted cells are those which are also being counted as Overdue. If they were still to be blank (not closed) in the month of August, currently my formula wouldn’t capture that it is still overdue in the month of August.

Similarly, if a problem report’s fix due date was July (7/1/13) and it did not get closed until August (8/2/13)—my report sheet does not count it as Overdue Closed for the month of August.
Do you know how I would fix my formula to do all that I need it to do?

I really appreciate your help Tom, you’ve been a huge help!


The way I see your requirement is to count all cells with a close date in the month of interest plus count all cells that had a Fix_Due_Date in the month of interest or earlier and have not close date.   Obviously the other criteria has to be met as well: TL_Severity and TL_CategoryNPR.  

so I would make two countif formulas that are mutually exclusive and add their result

COUNTIFS(CloseDate,">="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures),1),CloseDate,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Major",TL_CategoryNPR, " Enclosures")

In the above and below I use CloseDate to be a named range similar to Fix_Due_Date (covers same rows but refers to column R [Close Date])

The second new formula is
COUNTIFS(Fix_Due_Date,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Major",TL_CategoryNPR, " Enclosures",CloseDate,"")

this checks that the fix_Due_Date is less than or equal to the month in question and that the close date is empty.

so I just add these results to produce

=COUNTIFS(CloseDate,">="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures),1),CloseDate,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Major",TL_CategoryNPR, " Enclosures")+COUNTIFS(Fix_Due_Date,"<="&DATE(YEAR(Report_MonthEnclosures),MONTH(Report_MonthEnclosures)+1,0),TL_Severity,"Major",TL_CategoryNPR, " Enclosures",CloseDate,"")

that is for Major.  Change Major to Minor for the other formula

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.