1

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! :)

3
  • holy odd socks, that is an epic formula!
    – user375162
    Mar 5, 2015 at 7:55
  • It is. I just don't want the workbook to suddenly fail working or "explode" because of this freaking formula. Mar 5, 2015 at 8:23
  • According to the help file (for Excel 2010) the maximum length of a formula is 32,767 characters (in the question there are 2749, so you have some room left) and Excel allows for 64 levels of nested functions. Technically, you won't "explode" Excel. ;-)
    – agtoever
    Mar 6, 2015 at 8:19

1 Answer 1

1

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".

Anna, Ben en Charles grades

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.

3
  • Hi, agtoever! Thank you so much for taking time to read my concern, and for providing these options. I really appreciate your effort to do so. Unfortunately, I don't have a Power Pivot installed in my Excel 2010. I'm wondering if I can do the same with a Pivot Table. Also, I think the INDIRECT function would really help me. However, the INDIRECT function is really new to me. I'm researching right now how to use the function. I'm really sorry if I seem too slow to absorb this. Nevertheless, your suggestion would really help me a lot. ^_^ Have a great day! Mar 6, 2015 at 3:42
  • @phantomthief I added a more elaborate explanation using an example similar to your case. I hope this helps explaining what I have in mind.
    – agtoever
    Mar 6, 2015 at 8:14
  • Sorry for the very late response. I rested the matter for a while. I tried your suggestion. ^_^ It helped me and I was able to utilize other Excel tools as well to achieve my goal. Thanks a lot! Mar 26, 2015 at 2:06

You must log in to answer this question.

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