Migrating a manual dataset to a Pivot Chart - Excel 2016 (Office365)

Hi All,

Pardon the hopefully naive question here. I have a table of data on a sheet that sums data together from an evolving set of data (i.e. the data can be replaced with new similar data but different row counts). I'm able to create a chart on this data when I manually create the pivot data but am unable to recreate this same chart by using a Pivot Chart. I'm hoping that it is just because I don't understand the utilization of Pivots too well.

Here's a screenshot of the more complete source data to help give you an idea:
Manual Data Chart and Source.png

In this case I have manually populated the Months, Raised/Resolved, Software Defect, Non-Software Defect data.... The values found are all "COUNTIFS" based upon the column and row information. It is essentially the COUNTIFS that need to be reproduced in a Pivot.
I.e: in Cells H4:I5 are the following cell formulas

=COUNTIFS(MonthRaised,$F4,DefectType,H$3)	=COUNTIFS(MonthRaised,$F4,DefectType,I$3)
=COUNTIFS(MonthResolved,$F4,DefectType,H$3)	=COUNTIFS(MonthResolved,$F4,DefectType,I$3)

I have a sample workbook that I'm happy to send if I could figure out how to attach it :-)

I'm hoping that I can get this setup in a PivotChart that will be able to dynamically update the data. Right now if a new data set is dropped in I have to manually go through and change the months... i.e. if I have a project that runs from Feb to July the data needs to be reset so to speak.

Thanks for any help that you may be able to provide.

-Rob

Thank you for the input AVK.

For some reason I'm unable to attach anything other than a picture. The file attachment dialogue doesn't have anything in it...

FileAttach.png

I'm sure its something on my end. I'd happily provide you with my example xlsx otherwise.

Rob

Learning that the attachment Icon doesn't work...

I've attached my example XLSX file using the technique I noticed in another thread.

Thank You for the assistance JeteMC. I actually started using Tables for the first time yesterday in some formulas so I see where you went with this. What I really like about your technique is that I can easily copy the formula from cell to cell rather than making each formula a set of unique references.

In the end, as my project continues to evolve rapidly, I've opted to use some VBA to populate the fields (Date and Raised/Resolved) dynamically based upon data in my source Tables as I've attached below (I have a configuration sheet that calls out start and end dates etc.) I'll be using your formulas approach to replace my existing formulas.

Now I just need to learn how to modify the ActiveCharts source data range ;-) Working on learning that now.

Thanks so much
Rob

Please Login or Register  to view this content.