Create a summary sheet for multiple worksheets based on multiple criteria with formulas

**Rbower333** · 06-03-2015, 05:23 PM

Hopefully this is the right forum, this is my first post. I have a workbook with 8 worksheets in it. I am looking to have a 9th sheet (the summary sheet) that will pull all the data from each of the sheets based on the value of 2 columns. For example, there is column A to G, with B having a date and G having a status (either "Open" or Closed"). On my summary sheet, I want to be able to input a date a status and then show the entire rows data for everything that matches across all the sheets. The one caveat is that I want to do it with formulas if possible. I have searched the web and found some different options using arrays and ranges the MATCH, INDIRECT, INDEX and COUNTIF functions, but nothing that really covers pulling the whole rows of data across multiple sheets. Also, this needs to be dynamic to so if more rows are added to the individual sheets, the summary sheet will pick those up as well and update. Is this possible without VBA? The columns on each sheet are identical and all formatted the same. Any help would be appreciated. Thank you!

I have attached a sample of the basic setup of the workbook

**daffodil11** · 06-03-2015, 06:27 PM

Hey, I live in Lancaster, PA!

The answer is yes, but not with the .xls file type you are using. You'd have to use the Excel standard 2010+ which is .xlsx.

First, you'll probably want to set a number of Defined Names.

I used Sh1Match =SUMPRODUCT((Sheet1!$A$2:$A$50=Summary!$B$1)*(Sheet1!$G$2:$G$50=Summary!$B$2)).
This counts the number of valid matches found on the first sheet.

For the second name, Sh2Match =Sh1Match+SUMPRODUCT((Sheet2!$A$2:$A$50=Summary!$B$1)*(Sheet2!$G$2:$G$50=Summary!$B$2))
For the third name, Sh3Match =Sh2Match+SUMPRODUCT((Sheet3!$A$2:$A$50=Summary!$B$1)*(Sheet3!$G$2:$G$50=Summary!$B$2))
And so on, with each iteration including the previous sheet's match count.

Then I made A5:

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(
    INDEX(Sheet1!B$2:B$50,SMALL(IF((Sheet1!$A$2:$A$50=$B$1)*(Sheet1!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3))),
    INDEX(Sheet2!B$2:B$50,SMALL(IF((Sheet2!$A$2:$A$50=$B$1)*(Sheet2!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh1Match))),
    INDEX(Sheet3!B$2:B$50,SMALL(IF((Sheet3!$A$2:$A$50=$B$1)*(Sheet3!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh2Match))),
    INDEX(Sheet4!B$2:B$50,SMALL(IF((Sheet4!$A$2:$A$50=$B$1)*(Sheet4!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh3Match))),
    INDEX(Sheet5!B$2:B$50,SMALL(IF((Sheet5!$A$2:$A$50=$B$1)*(Sheet5!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh4Match))),
    INDEX(Sheet6!B$2:B$50,SMALL(IF((Sheet6!$A$2:$A$50=$B$1)*(Sheet6!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh5Match))),
    INDEX(Sheet7!B$2:B$50,SMALL(IF((Sheet7!$A$2:$A$50=$B$1)*(Sheet7!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh6Match))),
    INDEX(Sheet8!B$2:B$50,SMALL(IF((Sheet8!$A$2:$A$50=$B$1)*(Sheet8!$G$2:$G$50=$B$2),ROW($A$2:$A$50)-1),ROWS(B$1:B3)-Sh7Match))),"")

This is an array formula confirmed with Ctrl+Shift+Enter and copied over and down as needed.

Note: The ranges used in my calculations only extend to ROW 50. You'll need to adjust as necessary to fit your data.

Gosh that was fun.