shorter way to sum multiple Index/Match results from multiple tabs? Excel 2010

flitzanu

New Member
Joined
Jun 26, 2013
Messages
5
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.

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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top