2

I have the following data which I wish to pivot:

Student    JoinDate    ReleaseDate
John       01/05/2011  05/05/2011
James      02/05/2011  04/05/2011

I would like to create a pivot table which shows me a count of students within a given date range:

01/05/2011  02/05/2011  03/05/2011  04/05/2011  05/05/2011
1           2           2           2           1

Now, the issue I have is that I want the pivot table to somehow expand out the dates, I can't figure out how to achieve this with my current data structure.

I can achieve it with the following structure:

Student    JoinDate    ReleaseDate   PivotDate
John       01/05/2011  05/05/2011    01/05/2011
John       01/05/2011  05/05/2011    02/05/2011
John       01/05/2011  05/05/2011    03/05/2011
John       01/05/2011  05/05/2011    04/05/2011
John       01/05/2011  05/05/2011    05/05/2011
James      02/05/2011  04/05/2011    02/05/2011
James      02/05/2011  04/05/2011    03/05/2011
etc...

But this technique doesn't scale very well given the amount of data in use, I'm easily hitting the maximum number of rows for a single sheet in the workbook.

Does anyone have any suggestions as to how I can create the desired pivot table without having a row for each date the student is 'active'..?

3 Answers 3

3

If you're willing to try a solution that doesn't involve pivot tables:

Remove the Pivot Date column. Arrange the date range in headers and use the AND() formula to determine if that date falls within the student's tenure. Your data will look something like this:

enter image description here

Here's how the formula in D2 looks like (note the absolute references). Just drag or copy the formula to the rest of the columns and rows, autofill should take care of the rest:

=AND(D$1>=$B2,D$1<=$C2)+0

Number format is set to this: "+";;

The formula for the total number of students per date range is pretty easy. Here's the formula for D26 above.

=sum(D2:D25)

To add columns, just copy the last column and change the date above. To add students, insert a blank row above the last student (in this case, above Banshee). Record a macro to make it easier for you to update the data. This way you don't have to manually adjust the SUM formula at the bottom row. You can sort the data by Student name, Join date or Release date; the formulas at the right should be fine.

Here's a copy of the spreadsheet shown above (requires MS Excel 2007): http://ge.tt/46auqAN

2
  • This is great. It would be wonderful to provide a sample formula in the text of the answer, not just in a download sample, in case that is later not available.
    – datatoo
    May 19, 2011 at 22:39
  • Thanks! I forgot that ge.tt links expire after 30 days. I updated the post to include the formulas. :D
    – Ellesa
    May 20, 2011 at 0:32
1

You can get these counts with an array formula. No need for Pivot at all.

Assuming JoinDate is in column B, ReleaseDate in column C, and just the two rows of data for this example.
Dates to sum against are in columns E onward: Formula in E2. Enter as an array formula (Press Ctrl-Shift-Enter rather than just Enter to complete the formula)

=SUM(((($B$2:$B$4<=E$1)*1+($C$2:$C$4>=E$1)*1)=2)*1)

Copy across row 2 for each date you want a total for

The formula looks better and is easier to maintain if you use Named Ranges for the Date data

=SUM((((JoinDate<=E$1)*1+(ReleaseDate>=E$1)*1)=2)*1)

Date ranges can extend beyond the existing data to allow for adding more latter. eg $B$1:$B$100
Whole column ($B:$B) works too, but will slow down the recalculation

By the way, if you were using Excel 2007 or latter a new function is available (enter as regular formula, no need for array):

=COUNTIFS(JoinDate,"<="&E$1,ReleaseDate,">="&E$1)
0

Might not suit your application, but you should try the Pivot function in MS Access this far superior to Excel

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .