Help guys!
I'm trying to come up with a very short formula to compute for multiple data across multiple sheets.
I have one workbook wherein I compile error reports I receive from several people. The error reports they send are the ones they see from 22 different materials. I would like to group the error reports by month starting from December 2014 to December 2015.
In each sheet, I created a separate column named "Status" to indicate whether each of the reported errors have already been "Uploaded," is on "Standby," was "Sent to Translator," etc. In a separate sheet, I listed the names of all the 22 materials in the first row, and then I listed down weekly dates in the first column.
Basically, what I would like to happen is to count for the errors under a specific material from December 2014 to December 2015 if and only if the errors fall under specific "Statuses" and the errors were sent within a specific timeframe (weekly dates, e.g., Feb23-March1).
So far, I'm using this very long formula:
=COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December 2014'!$J:$J,"Applied",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Applied",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Applied",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Applied",March!$G:$G,"="&C$1)+COUNTIFS('December
2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December
2014'!$J:$J,"Sent to Translator",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Sent
to
Translator",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Sent
to
Translator",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Sent
to Translator",March!$G:$G,"="&C$1)+COUNTIFS('December
2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December
2014'!$J:$J,"Waiting for Upload",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Waiting
for
Upload",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Waiting
for
Upload",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Waiting
for Upload",March!$G:$G,"="&C$1)+COUNTIFS('December
2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December
2014'!$J:$J,"Uploaded",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Uploaded",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Uploaded",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Uploaded",March!$G:$G,"="&C$1)+COUNTIFS('December 2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December
2014'!$J:$J,"Audio",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Audio",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Audio",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Audio",March!$G:$G,"="&C$1)+COUNTIFS('December
2014'!$A:$A,">="&$A15,'December 2014'!$A:$A,"<="&$B15,'December
2014'!$J:$J,"Standby",'December
2014'!$G:$G,"="&C$1)+COUNTIFS(January!$A:$A,">="&$A15,January!$A:$A,"<="&$B15,January!$J:$J,"Standby",January!$G:$G,"="&C$1)+COUNTIFS(February!$A:$A,">="&$A15,February!$A:$A,"<="&$B15,February!$J:$J,"Standby",February!$G:$G,"="&C$1)+COUNTIFS(March!$A:$A,">="&$A15,March!$A:$A,"<="&$B15,March!$J:$J,"Standby",March!$G:$G,"="&C$1)
That, so far, is from December 2014 to March 2015 only. I can't imagine how long will it get once I included the other months.
Is there any way I can make this formula shorter?
Thank you very much in advance! 🙂
Best Answer
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
INDIRECTfunction 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 yourCOUNTIFformula, using theINDIRECTfunction 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:
Cell references:
The translation to your problem is as follows:
Basically the trick is to list the sheet names in a list and use that in combination with the
INDIRECTfunction to reference to the different months. If you do the same for the different statusses, you get a matrix. Count (usingCOUNTIFS) 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.