Sumifs Indirect Contains Index Match help

[Iggy 2]

I have a doozie formula that i need help with. If you kind people would

My predicament, i am working on a summary sheet that adds values based on specific columns in specific sheets, the rows are all the same.

For example i need to add all of the row 3 data for columns labeled "JAN" in sheets whose name start with "F-"

So i don't know if i can do a combination of sumif ( indirect ( Contains"F-") but at that point i am lost as to if i can use some sort of index match in which the row is 3 and the column matches "JAN"

 

[jasonb75]

It might be possible but it would be far from practical.

First of all you would need a list of the sheets meeting the criteria, assuming that there will be various criteria, this would mean a list of all sheet names, then a second list that would use array formulas to isolate those meeting the criteria.

With a formula in a single cell that will have variable criteria this might not be too bad, but with multiple formulas each having different criteria, you would need a list for each criteria.

Normally this would be done with named ranges, but with the potential complexity of the task it could easily get out of hand. Without named ranges the number of repeated arrays with volatile functions required would likely make the workbook unusable.

 

[Iggy 2]

Thanks for the response. With your insight i had a different idea, in which i list out all the sheet names down and the months across, since i only needed 2 rows in particular it ended up not being that big. then i just added them up together per month in that sheet and then matched it to my summary tab. The formula i ended up using is =IFERROR(INDEX(INDIRECT("'"&$A4&"'!"&"$A$4:$AO$60"),17,MATCH(B$3,INDIRECT("'"&$A4&"'!"&"$A$4:$AO$4"),0)),0)

In wich A4 is the sheet name and B3 is the month.

Im just going to have to add rows to that file as i add more sheets. but it shouldnt be too bad to update since sheets for 4 years is only 40, so every year ill take out 10 and add 10. no problems!

Thanks for your help.