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

spcalan

Well-known Member
Joined
Jun 4, 2008
Messages
1,247
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
Joined
Jun 4, 2008
Messages
1,247
Is index(match) limited to 2 variables?

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

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
Joined
Jun 4, 2008
Messages
1,247
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
Joined
Sep 6, 2002
Messages
9,123
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top