Defect histogram

**DKupfrian** · 05-10-2016, 04:29 PM

I have hundreds of defects in a sheet with:
Column A: Open date
Column B: Closed date
Column C: Severity

I would like to create a stacked line chart that would show date on the x axis and on the y axis the volume of each severity(minor, serious, critical) that were open that day.

I have not found any out of the box answers for this.

Thanks in advance for any pointers you can give me.

**MrShorty** · 05-10-2016, 05:12 PM

It is not clear to me what you need. If I understand correctly, my first thought would be to use a pivot table to calculate the volume for each type of severity on each day. Then, use the pivot table to create your stacked line chart. I would expect the table to look something like:

Please Login or Register  to view this content.

If it turns out that a pivot table is not suitable, then use a COUNTIFS() function to build the same table. Then select the table and add your stacked line chart.

**DKupfrian** · 05-11-2016, 08:39 AM

This will be easier to explain with an example. Each of the four lines below represents a defect which was opened on the first column, closed on the second column, and the severity of the defect in the third coulmn:
5/1/2016 5/5/2016 Minor
5/2/2016 5/4/2016 Minor
5/2/2016 5/4/2016 Serious
5/3/2016 5/3/2016 Critical

Using the data above I would like a chart to show:
5/1/2016 (1 Minor)
5/2/2016 (2 Minor) (1 Serious)
5/3/2016 (2 Minor) (1 Serious) (1 Critical)
5/4/2016 (2 Minor) (1 Serious)
5/5/2016 (1 Minor)

HistoSample.PNG

**DBY** · 05-11-2016, 11:09 AM

Hello
As Mr.Shorty suggested, you can use COUNTIFS to summarize the data to create your chart. See the attached file. The formulas are in the grey cells. Note these dates are in UK format: dd/mm/yy.

Hope this helps.
DBY