How to find Avg. number of days to close incidence, how many pending and upheld

[Dan_Excel]

thanks in advance. ** Sorry I was unable to attach a file **

As per the attached I need to find out:

I've managed to count the number of incidences open and closed each month, Rows 5 and 6 but I don't know how to combine it with the rest.
- how long is it taking in avg. to close incidences per month? Tab "Summary", row 7.
- how many "Pending" incidences we have for each month, this is something that would need to be updated on a monthly basis and should tend to be 0 for the initial months since after some time, they all should be resolved.
- Cumulative incidences upheld per month.

Thank you

[Pepe Le Mokko]

In the future please post in the correct forum. I moved it for you
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Dan_Excel]

Thank you so much Pepe, file attached now!!!!

[JeteMc]

Hello Dan_Excel and Welcome to Excel Forum.
As to "how long is it taking in avg. to close incidences per month?" try:

Please Login or Register  to view this content.

As to the other two questions, it may help if you could manually fill in at least some of the values so that we will know the expected output for the formulas/code.
Let us know if you have any questions.

[Dan_Excel]

Thanks so much, It works!!

With the 2nd question don't worry is will have to done manually since it will change every month.

With the 3rd one, I have attached the file with additional registers, and I'll expand on my explanation:

Row 9 - Summary Tab
I'm trying to count the incidences that were classified as "Upheld" since the beginning of the register till the date specified in rows 2 and 3.
I tried something like:

=IF(SUMPRODUCT(MONTH(IncidenceLog!$B$2:$B$498)<=O4)*(YEAR(IncidenceLog!$B$2:$B$498)<=O2),COUNTIF(IncidenceLog!$C$2:$C$498,"upheld"),"")

But it results in everything blank.

Many thanks again, you're very helpful.

[JeteMc]

I would be easier to help if values were manually placed in some of the cells in row 9 so that we could know what values the formula/code should yield.
That said I feel as if the following formula will provide what you want: =SUMPRODUCT((IncidenceLog!$B$2:$B$25<=EOMONTH(C4,0))*(IncidenceLog!$C$2:$C$25="yes"))
Note that row 4 is now populated with actual dates. If you want only the month numbers displayed in that row select cells C4:U4, press the Ctrl and 1 keys, select custom, in the Type: window put m and select OK
Let us know if you have any questions.