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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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