Index/match?

**Koda7** · 05-25-2011, 06:41 PM

I'm creating a file in which I need a formula. I'm currently using the following formula, however it makes the file huge and I know isn't the best wasy to do it. Also, I'm using Excel 2010, and I need others that use Excel 2003 to view it. Because of the formula I used, Excel 2003 doesn't support it.

=SUMPRODUCT(--(Data!$C$1:$C$3000=$C11),--(Data!$F$1:$F$3000=$D11),--(Data!$E$1:$E$3000=$C$4),--(Data!$H$1:$H$3000=$E$3),--(Data!$G$1:$G$3000=$D$4),(Data!$I$1:$I$3000))

The file will be updated weekly with data (about 3000 rows) and will be consistantly in the same columns. Because of the various criteria I must use, and all the different ways I need to look at the data, I need a formula that will allow me to view the data I need, yet be able to work in Excel 2003 as well. I can't use a pivot table for this as I need to add more formulas and will also be pulling certain cells into other spreadsheets every week.

I've attached a sample file (not with all the dimensions, because there are so many). The blue highlighted cells are the criteria I must look up, then return the results from Column I. I will also be using the same criteria to return the results from Column J-N.

The cell I need the formula for is orange in cell E11.

The header rows and columns on the summary tab will remain constant, however, cell C4 & D4 will change as I create more tabs.

Any help will be greatly appreciated! Thank you!