Sumproduct vs ???? ( index(match)?

[spcalan]

Currently I have a data sheet:

5 Columns - Week/Plant/Type/Capacity/Pounds Shipped.

I currently have a sumproduct formula to add up all Pounds shipped by a certain week, certain plant, certain type and then divide by the capacity by the same certain week, certain plant, and certain type to get a %.

I am just now digging into index(match) formulas, but do not know if this is the way to go?

Here is my current formula:
=SUMPRODUCT((Data!J$2:J$64997=Danville!A3)*(Data!B$2:B$64997=Danville!B$1)*(Data!E$2:E$64997=Danville!C$1)*(Data!H$2:H$64997))/SUMPRODUCT((Data!J$2:J$64997=Danville!A3)*(Data!B$2:B$64997=Danville!B$1)*(Data!E$2:E$64997=Danville!C$1)*(Data!F$2:F$64997))*100

Any suggestions?

 

[spcalan]

Is index(match) limited to 2 variables?

Can I use Index Match for week/plant/type??

 

[DonkeyOte]

If you have multiple criteria then SUMPRODUCT is usually the right way to go... however given your example your ranges are HUGE (64997 rows) which will have adverse impact on performance... if you do have that many rows of data so be it but you should probably be thinking along the lines of using a purpose built database to store your data (Access,MySQL,SQLSvr)... and then using SQL (with ADO) to do your calculations ... if you don't have that many rows then reduce your ranges in line with actual data.

 

[spcalan]

I agree, but the higher ups want it in Excel.
Access was my first choice.

I use 65,536, so I dont always have to change the ranges.

 

[DonkeyOte]

give yourself some *room* but don't go to those extremes... if you only have 1 sumproduct it's not so bad but if you had a few all utilising the same range it will impact performance.

it's an all too common problem that management are inclined to dictate best practice with little ideal of reality... Excel is not a database and should not really be used as such... that's why MS gave them Access.