SUMPRODUCT Formula

[Vanessa Simmonds]

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

 

[Max]

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]

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]

.. 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",B11,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
---

 

[Justin Larson]

=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.