Microsoft Excel – Transform Spreadsheet Data

I think there are at least two viable options. Based on your circumstances, one might be more suitable than the other. I'll give you the two options I could think of and see if you can work from there.

Option 1: Power Pivot
Microsoft's Power Pivot lets you combine multiple sources of similar data and create one pivot table from it. If you add each worksheet as data source, and addend it to your data, you can pivot and filter according to your criteria.

Option 2: Use INDIRECT
You can use the INDIRECT function to apply your criteria. If I understand you correctly, I'd make a matrix, containing the months (worksheet names) on the vertical axis and the status (Applied, Sent, Waiting for upload, ...) on the horizontal axis. Next, you can fill your matrix with your COUNTIF formula, using the INDIRECT function to reference to the different worksheets and apply for the different statuses. For example if you have "December 2014" in A2, INDIRECT("'"&A2&"'!$A:$A") would refer to 'December 2014'!$A:$A. Next you just add the values horizontally and vertically to create subtotals per status and per month. The sum of the entire matrix is your desired outcome.

Edit:
option 2 explained:
As requested by the OP, here is a bit more in-depth explanation of the second option. To illustrate, I'll introduce Anna, Ben and Charles, who follow three courses. They get several grades (ranging from 1 to 10) for each course. Every grade counts for a particular trimester. The lector of the course wants to compare the number of sufficient (>5.5) grades for each student in each course for the first trimester. Here are the different courses (each course is in a separate worksheets, called "Course1", "Course2" and "Course3".

Similar to your problem, we have several criteria (>5.5, trimester = 1, count for several courses as well as for several students). The topleft cell inside the matrix (Course1 x Anna, in my sheet this is in B5) contains the following formula:

=COUNTIFS(INDIRECT($A5&"!A:A");B$4;INDIRECT($A5&"!B:B");$C$2;INDIRECT($A5&"!C:C");$C$1)

Cell references:

• A5 points to the cell containing "Course1"
• B4 points to the cell containing "Anna"
• C2 points to the cell containing "1" (for the trimester)
• C1 points to the cell containing ">5,5" (for the grade)

The translation to your problem is as follows:

• Courses --> months
• Student names --> Status
• And the rest is trivial

Basically the trick is to list the sheet names in a list and use that in combination with the INDIRECT function to reference to the different months. If you do the same for the different statusses, you get a matrix. Count (using COUNTIFS) the values you want to count. The grand total of the values in the matrix is the total number (summed for different months and different statusses) you are looking for.

Another way to put it: instead of forging a complex formula yourself, try to let Excel do the work for you.

I assume that your data look like this:

_{(1) I formatted columns E and F as mmm-yy (m/yyyy) to avoid language-based confusion.}
^{(2) There is a copyable version of the above in this answer’s source.}

on Sheet1, and that you want to copy it to Sheet2 with the extra rows added.  You can do that with three “helper columns” on Sheet2 — in the below steps, I use X, Y, and Z.  Here’s how to do it:

• Copy the column headings from Sheet1, row 1, to Sheet2, row 1.
• Enter =IF($Y2=0, INDEX(Sheet1!A:A, $X2), "") into Sheet2!A2 and drag/fill to the right to cover all your data (i.e., to column I).
• Copy Sheet1:A2:I2 and paste formats onto Sheet2:A2:I2.
• Change Sheet2!E2 (begin month) to
      =DATE(YEAR(INDEX(Sheet1!E:E, X2)), MONTH(INDEX(Sheet1!E:E, X2))+Y2, 1).
• Enter 2 in Sheet2!X2.  This designates the row on Sheet1 that this row (on Sheet2) will pull data from, so, for example, if your data actually begin in row 61 on Sheet1, enter 61 in Sheet2!X2.
• Enter 0 in Sheet2!Y2.
• Enter =INDEX(Sheet1!F:F, $X2) into Sheet2!Z2.  (If you want, format it as a date.)
• Select Sheet2!A2:Z2 and drag/fill down to row 3.
• Change Sheet2!X3 to =IF(E2<Z2, X2, X2+1).
• Change Sheet2!Y3 to =IF(E2<Z2, Y2+1, 0).
• Select Sheet2!A3:Z3 and drag/fill down as far as you need to get all your data.

It should look something like this:

Notes:

• As stated in the instructions, Sheet2!Xn specifies the row on Sheet1 that row n (on Sheet2) will pull data from.
• Sheet2!Yn is a one-up number within a Sheet2!Xn value; i.e., within a Sheet1 row.  For example, since rows 3-6 on Sheet2 pull data fromSheet1 row 3, we have X3=X4=X5=X6=3, and Y3, Y4, Y5, Y6 = 0, 1, 2, 3.
• Column Z is just the “true” branch of the IF expression in column F; i.e., the end month for this group of rows.

Of course you can hide columns X, Y, and Z.  Or, if you want to do this just once and be done, you can copy and paste values.