# Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria

1. ## Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria

My original Problem was creating a 3d lookup with multiple criteria. I did this originally all in one workbook where the sheet labeled "Summary" contained the formulas to complete my lookup.

The goal of "Summary" was to be able to control the entire sheet by entering the month (abbreviated) that I want data for into cell A2.

To do this I first created the formula below and entered it into Cells B4:X4. This formula looks at Summary A2 and matches the value (month) with the sheet (Jan:July which is written grouped as "'"&list&"'" in Summary A8:A14) that holds the same value (month) in cell A2. When the correct sheet is found it takes the values from B4:X4 and enters them into Summary B4:X4.

=IF(SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!\$A\$2"),\$A\$2,INDIRECT("'"&list&"'!b\$4")))=0,"",(SUMPRODUCT( SUMIF(INDIRECT("'"&list&"'!\$A\$2"),\$A\$2,INDIRECT("'"&list&"'!b\$4")))))

That process was necessary because the dates are not straight through, they are only business days which change every month.

After that I created the formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!\$B\$4:\$X\$4"),B\$4,INDIRECT("'"&list&"'!B\$14:X\$14")))

this looks through all the sheets until it finds the date that matches (purpose of previous formula) and then enters the value of the appropriate sheet cell B14

I have the workbook working perfectly. All that needs to be done is change the month in worksheet 'Summary' cell A2 and the worksheet, finds the correct dates, and the correct values.

I was very excited to get this correct but now I have a new problem.

The sheet labeled Summary CAN NOT be part of the workbook BrianFormulas.xlsx, it needs to its own workbook. Also creating a worksheet within BrianFormulas that holds all the dates is out of the question.

So basically I really need some help here. I need to essentially use the same formulas but have them looking through the workbook BrianFormulas.xlsx from the workbook Brian Summary.xlsx

Also i'm not positive but pretty sure that Indirect does not work if the workbook it is looking at is closed. Please let me know if that is true and a way to resolve this issue.

I need to finish this project by tomorrow so any help would be greatly appreciated  Register To Reply

2. ## Re: Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteri

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.  Register To Reply