Hey folks, this is a bit of an oddball question here. With much help from trolling this forum and others I've learned a lot, but often I just modify what I can find and make it do what I want 
I'm wondering though, if anyone has any opinion or advice on my formulas I'm going to post, because I'm curious if there are simpler ways to do what I'm doing.
Mind you, these formulas work and do exactly what I need, but I'm just figuring there must be a simpler or shorter way to enter all this.
so, this is just the first entry, basically this is for "mo1" or month1, and so in each line I'm changing that to mo2, mo3, etc...and summing them all together. So the same line above is repeated up to 12 times, changing only the "mo1" to "mo2" and so forth.
The data I'm adding is never in the same spot on each sheet, so the most accurate way was to match it and sum to avoid errors.
Also, in the formula it is designed to default to a "0" if it finds an error, or if the result is a "0" or if it is not a number, otherwise it returns a "1". (basically, it is looking for a valid number, and if it exists it is a "1" and each "1" is summed into a "total number of months" to be used for an average)
Now, along the same lines, this is a very similar formula, and it may be a little easier...it is the same type of "summing multiple index/match results" but without the complication of the 1 and 0 stuff above:
Again, there's the first line, and each subsequent line would be changing the "mo1" to "mo2" etc, and each of the results are being summed for a total.
So instead of going through and doing all this by hand and having REALLY big formulas, can anything be done to modify this to do any type of array, or anything to make it work easier or faster to fill in the "mo(1,2,3,4,5)" stuff? If that makes sense?
i had an array like this one below, but it is very simple and only counting one very specific item on each sheet, but it made me wonder if something like that could be applied to the type of formulas above.
Any thoughts or ideas would be much appreciated! Oh, i'm also in Excel 2010.
I'm wondering though, if anyone has any opinion or advice on my formulas I'm going to post, because I'm curious if there are simpler ways to do what I'm doing.
Mind you, these formulas work and do exactly what I need, but I'm just figuring there must be a simpler or shorter way to enter all this.
Code:
IF(ISERROR(IF(N(INDEX('mo1'!$AJ$2:$AJ$222,MATCH(Total12!$A2,'mo1'!$A$2:$A$222,0),0))>0,IF(ISNUMBER(INDEX('mo1'!$AJ$2:$AJ$222,MATCH(Total12!$A2,'mo1'!$A$2:$A$222,0),0)),1,0),0)),0,IF(N(INDEX('mo1'!$AJ$2:$AJ$222,MATCH(Total12!$A2,'mo1'!$A$2:$A$222,0),0))>0,IF(ISNUMBER(INDEX('mo1'!$AJ$2:$AJ$222,MATCH(Total12!$A2,'mo1'!$A$2:$A$222,0),0)),1,0),0))so, this is just the first entry, basically this is for "mo1" or month1, and so in each line I'm changing that to mo2, mo3, etc...and summing them all together. So the same line above is repeated up to 12 times, changing only the "mo1" to "mo2" and so forth.
The data I'm adding is never in the same spot on each sheet, so the most accurate way was to match it and sum to avoid errors.
Also, in the formula it is designed to default to a "0" if it finds an error, or if the result is a "0" or if it is not a number, otherwise it returns a "1". (basically, it is looking for a valid number, and if it exists it is a "1" and each "1" is summed into a "total number of months" to be used for an average)
Now, along the same lines, this is a very similar formula, and it may be a little easier...it is the same type of "summing multiple index/match results" but without the complication of the 1 and 0 stuff above:
Code:
=N(IFERROR(INDEX('mo1'!$D$2:$D$222,MATCH(Total12!$A2,'mo1'!$A$2:$A$222,0)),0))Again, there's the first line, and each subsequent line would be changing the "mo1" to "mo2" etc, and each of the results are being summed for a total.
So instead of going through and doing all this by hand and having REALLY big formulas, can anything be done to modify this to do any type of array, or anything to make it work easier or faster to fill in the "mo(1,2,3,4,5)" stuff? If that makes sense?
i had an array like this one below, but it is very simple and only counting one very specific item on each sheet, but it made me wonder if something like that could be applied to the type of formulas above.
Code:
=SUMPRODUCT(COUNTIF(INDIRECT("mo"&{1,2,3,4,5,6,7,8,9,10,11,12}&"!$A$2:$a$500"),$A2))Any thoughts or ideas would be much appreciated! Oh, i'm also in Excel 2010.
