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

#### spcalan

##### Well-known Member
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?

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### spcalan

##### Well-known Member
Is index(match) limited to 2 variables?

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

#### DonkeyOte

##### MrExcel MVP
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

##### Well-known Member
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

##### MrExcel MVP
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,990
Messages
5,834,746
Members
430,315
Latest member
bobh63

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back