Excel 2010 Pivot table

[DizzyMoe]

Hi I have a data source with a unique identifier, created date, resolved date, current status, etc. How do I create a pivot table/chart that shows the volume create and resolved monthly for a particular period (say July 2011 to March 2012)?

[Paul]

Hi Moe, welcome to the forum.

I would think you could drag the Resolved Date field to the Row Headers section, then drag the Unique Identifier field to the Value section to get a count by resolved date. Then click any of the Resolved dates and choose Group.. and select Month. (Deselect any others by clicking on them to remove the highlighting.)

[DizzyMoe]

Thanks for the response, Paul. I need both the created and resolved on the same chart. What I failed to state previously is there is a data connection and if the resolved date is null, my data connection inserts "1/01/1970 8:00:00 AM" so by counting it, the count is incorrect.

[Paul]

Perhaps then:

Assuming your data is in A2:D__, in E1 put a new header like Resolved2, then in E2 use the formula:

=IF(D2="",NA(),D2)

Fill that down. Create the PT and drag the Resolved2 field to the Row Headers section, and Unique ID field to the Values section. (Change from Sum to Count, of course.) Finally, click the drop-down on the Resolved2 field header and select Filter. Un-check the entry for #NA and your totals should be correct.

Hopefully that works!

[DizzyMoe]

I've attached a sample file. It includes a pivot table which is not what I am after. What I need is to show the volume of calls that were created by Year/Month (easy) and the calls resolved by Year/Month. The calls resolved volume is to be regardless of when the call was created.

Thank you in advance.

[Paul]

Hmm, having a tough time trying to get that to work.. it may just be easier to use a summary table. See attached (chart not updated, but summary table is on Data sheet.)

Your original data is in columns A:E. In H1-K1 put the headers Year, Month, Num Created, Num Resolved. In column H put 2010 (12 times), 2011 (12 times) and 2012 (again, 12 times). In cell I2 put 1/1/2010 (Jan 1, 2010), then in I3 put 2/1/2010 (Feb 1, 2010). Use Fill > Series (or the Fill Handle) to fill down through 2012 (the first of each month should appear).

In J2 use the formula:

=SUMPRODUCT(--(YEAR($C$2:$C$1000)=H2),--(MONTH($C$2:$C$1000)=MONTH(I2)))

In K2 use:

=SUMPRODUCT(--(YEAR($E$2:$E$1000)=H2),--(MONTH($E$2:$E$1000)=MONTH(I2)))

Fill those down for each year/month combination, and then build your chart off of that data.