Count Incident Age (Weeks) Based on Severity and Status

[melzjm]

Hi All,


I need your help because I am new to this job. I have a table (figure 1.1) and I need to arrive to a result for my weekly report (1.2). I have tried pivoting but I cant count the 4+ weeks. This report format was given by my boss.


Please see example below.


https://docs.google.com/document/d/17hS0RbyZ7rcAbundJ48N5q4CBpeyYoDqQ-N6rkwwIP0/edit


Hope someone can shed me some light. It will be really helpful cause I am still trying to find a way with excel.


Thanks.

 

[NARAYANK991]

Hi ,


I have not seen whether this can be done using Pivot tables or not. However , it can be done using formulae.


Check out the worksheet at the following link :


https://docs.google.com/spreadsheet/ccc?key=0AkKMpuzr3MTVdDhRTVJLcVl0X3RMaGpRX1owblN5aFE


I am assuming you have Excel 2007 or later.


Narayan

 

[kchiba]

Hi


Set up your table as follows: Note the cell with <>Closed and >4


<>Closed Open

Sev Closed 1 2 3 >=4

P1 0 1 1 0 1

P2 1 0 0 0 1

P3 1 0 0 0 1

P4 0 0 1 1 1

2 1 2 1 4


I cannot line the above table correctly in this post, "<>Closed" is in the Cell above "Closed"

You now need 2 formulas, One for the column Closed and One for the Other columns.


The formula for the closed column is =COUNTIFS($B$2:$B$11,$D$16,$C$2:$C$11,$C17)

and for the rest =COUNTIFS($B$2:$B$11,$D$15,$C$2:$C$11,$C17,$D$2:$D$11,E$16)


This is assuming that the above is in the range C15:H21. With <>Closed in D15, Closed in D16, P1-P4 in Range C17:C20


You will need Excel 2007 or higher as the COUNTIFS function is being used here


Try this link http://dl.dropbox.com/u/48309468/Severity.xlsx

 

[melzjm]

Hi kchiba & narayank991,


Thank you so much for your help, I have tried both solutions and it worked! I am so happy to learn something new and I very thankful for people like you who gives their time to help.


God bless you.

 

[SirJB7]

@kchiba

Hi!

For proper formatting of sheet-alike values embed the copied range within back-ticks

`.

Regards!

 

[NARAYANK991]

Hi melzjm ,


Thanks. Happy to help. Keep visiting.


Narayan