Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Formula
Good Morning,
I have a big spreadsheet which is populated by sumproduct formulas. The spreadsheet has about 173 rows and 15 columns. Every cell is populated by at least two sumproduct formulas. This is making the workbook extremely slow and most of the time is crashes. Is there anything i can do to make it quicker? Thank you very much. Vanessa Simmonds |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Formula
Is there anything i can do to make it quicker?
2 thoughts: 1. Set the book to Manual calc mode (via Tools Options Calculation tab). Then you can data-enter, etc w/o recalc getting in the way, and only press F9 to recalc after each batch of data entry is completed. Take a short break when you press F9. 2. Edit the source ranges involved in the sumproduct. Use the smallest possible range sizes which are just large enough to cover the possible extent. Example, don't cover 65k rows when it highly unlikely that source data will ever populate to that extent. Use just enough to cover. Keep ranges smallest possible. Above helps? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Vanessa Simmonds" wrote: I have a big spreadsheet which is populated by sumproduct formulas. The spreadsheet has about 173 rows and 15 columns. Every cell is populated by at least two sumproduct formulas. This is making the workbook extremely slow and most of the time is crashes. Is there anything i can do to make it quicker? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Formula
Hi Max,
Thank you very much for your help. I have just one more quick question: How can i use sumproduct, and and or formulas together? I want my sumproduct formula to sum the product of five different variable. See example below: Customer X Customer Y Customer z Shampoo 200mL 10 40 70 Conditioner 250mL 20 50 80 2in1 400mL 30 60 90 So I want a formula that will sumproduct Shampoo 200mL Customer Y + 2in1 400mL Customer Y. I tried to use Sumproduct(and( but it didn't work. Thank you very much for your attention. "Max" wrote: Is there anything i can do to make it quicker? 2 thoughts: 1. Set the book to Manual calc mode (via Tools Options Calculation tab). Then you can data-enter, etc w/o recalc getting in the way, and only press F9 to recalc after each batch of data entry is completed. Take a short break when you press F9. 2. Edit the source ranges involved in the sumproduct. Use the smallest possible range sizes which are just large enough to cover the possible extent. Example, don't cover 65k rows when it highly unlikely that source data will ever populate to that extent. Use just enough to cover. Keep ranges smallest possible. Above helps? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Vanessa Simmonds" wrote: I have a big spreadsheet which is populated by sumproduct formulas. The spreadsheet has about 173 rows and 15 columns. Every cell is populated by at least two sumproduct formulas. This is making the workbook extremely slow and most of the time is crashes. Is there anything i can do to make it quicker? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Formula
.. a formula that will sumproduct Shampoo 200mL Customer Y
+ 2in1 400mL Customer Y .. Assuming your data as posted in cols A to D where in B1 across are the customers: Customer X, etc in A2 down are the products: Shampoo 200mL, etc then this expression will calculate what you seek: =SUMPRODUCT(--ISNUMBER(MATCH(A2:A10,{"Shampoo 200mL";"2in1 400mL"},0)),OFFSET(A2:A10,,MATCH("Customer Y",B1:D1,0))) Adapt the ranges to suit Celebrate? Click YES below P/s: Pl post new queries as new threads in future -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Vanessa Simmonds" wrote: Hi Max, Thank you very much for your help. I have just one more quick question: How can i use sumproduct, and and or formulas together? I want my sumproduct formula to sum the product of five different variable. See example below: Customer X Customer Y Customer z Shampoo 200mL 10 40 70 Conditioner 250mL 20 50 80 2in1 400mL 30 60 90 So I want a formula that will sumproduct Shampoo 200mL Customer Y + 2in1 400mL Customer Y. I tried to use Sumproduct(and( but it didn't work. Thank you very much for your attention. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT Formula
=sumproduct(--(range1=criteria1),--(range2=criteria2),(sumrange))
array enter, use as many criteria as you want in that format. Make sure all your ranges cover the same number of rows - so if range 1 = A1:A9, then range 2 and sumrange should be x1:x9 also. "Vanessa Simmonds" wrote: Hi Max, Thank you very much for your help. I have just one more quick question: How can i use sumproduct, and and or formulas together? I want my sumproduct formula to sum the product of five different variable. See example below: Customer X Customer Y Customer z Shampoo 200mL 10 40 70 Conditioner 250mL 20 50 80 2in1 400mL 30 60 90 So I want a formula that will sumproduct Shampoo 200mL Customer Y + 2in1 400mL Customer Y. I tried to use Sumproduct(and( but it didn't work. Thank you very much for your attention. "Max" wrote: Is there anything i can do to make it quicker? 2 thoughts: 1. Set the book to Manual calc mode (via Tools Options Calculation tab). Then you can data-enter, etc w/o recalc getting in the way, and only press F9 to recalc after each batch of data entry is completed. Take a short break when you press F9. 2. Edit the source ranges involved in the sumproduct. Use the smallest possible range sizes which are just large enough to cover the possible extent. Example, don't cover 65k rows when it highly unlikely that source data will ever populate to that extent. Use just enough to cover. Keep ranges smallest possible. Above helps? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Vanessa Simmonds" wrote: I have a big spreadsheet which is populated by sumproduct formulas. The spreadsheet has about 173 rows and 15 columns. Every cell is populated by at least two sumproduct formulas. This is making the workbook extremely slow and most of the time is crashes. Is there anything i can do to make it quicker? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with SUMPRODUCT formula | Excel Discussion (Misc queries) | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula | Excel Worksheet Functions | |||
Sumproduct Formula Help | Excel Discussion (Misc queries) | |||
SumProduct Formula | Excel Worksheet Functions |