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