I have a Summary sheet that references other sheets in the workbook that are updated monthly by an external party.
The sheets are inconsistent in the number of rows and columns but there are values in the columns with the same name across sheets which don’t always appear in column D, sometimes they appear in column F or AZ for example but the sheet names and column names that I need to pull the data out of are always the same. This led me to INDEX, MATCH, MATCH but then...
When values in column A (which contains a list of names) meets a certain criteria e.g. if name contains “DBN”, I then need to validate whether there is an “x’” on the same row in a column named “Blue” and where both these criteria are met, add up all the values in the column named “Cost” on the Summary sheet.
I tried creating an array by referencing Sheet2!$A:$A as the entire column then using INDEX, MATCH, MATCH for the entire sheet (Sheet2!$1:$1048576) but Excel runs out of memory as it’s parsing the entire sheet across 5 sheets in multiple functions and crashes.
I tried creating an array automatically based on cells with data in them using COUNTA in a formula but couldn’t get it to work. Then I started reading up about the pitfalls of using arrays and decided that there are better ways to pull the data, like SUMPRODUCT / SUMIFS combined with INDEX, OFFSET and MATCH to no avail.
I’m sure there must be a more elegant way of doing this. PowerPivot possibly?
Cost with multiple criteria in non standard sheets.xlsx
Many thanks
Bookmarks