Sumifs Indirect Contains Index Match help

Iggy 2

New Member
Joined
Jun 28, 2016
Messages
32
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"
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,994
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2016
Messages
32
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,021
Messages
5,545,542
Members
410,690
Latest member
navneetr
Top