can't use A:A or B:B for sumproduct

[samilator]

hello.
i'm really fond of using sumproduct and sumif functions for my stoc
inventories.

i use sumif for one condition formulas, and sumproduct for formula
that require more than one condition - such as when i have to meet th
stock no. size and color.

MY PROBLEM is, when i have to use sumproduct, unlike sumif, i CANNO
use A:A inside the formula. i have to use A1:A5000. and this reall
slows excel down. sometimes it even freezes on me.
i really cannot use lesser values such as A1:A400 since inventor
entries and done everyday and it really accumulates overtime.

can anyone help please? thanks!!

-samm

 

[Arvi Laanemets]

Hi

Define ranges used in sumproduct as dynamic named ranges - so the ranges
will always have optimal size.

 

[samilator]

let me give you a clearer picture.
i have many products with different stock nos., sizes, and color.
also many different customers. what i want to do is to create a lis
that would summarize which customers ordered which product, the siz
and the color, and how much each ordered. this summary would be mad
from another sheet where i input sales transactions.
so on the summary sheet2 i made the list of all the stock nos. o
column 1, their size on column 2, and the color on column 3. and on th
top row i indicated the customer name.
the formula i use would be something like sumproduct(
--( sheet1stockcolumnA$1$:A5000=sheet2column1),
--(sheet11sizecolumnA$1:$A5000=sheet2column2),
--(sheet1customercolumnA$1:A$5000=sheet2row1),
sheet1amountcolumA$1:A5000$)

and then i fill down to all the cells below.
and then excel freezes.
is there a better way to do this?
thank

 

[samilator]

thanks. but it still freezes. i've got about 12000 cells in a singl
sheet all containing sumproducts with 4 conditions linking to anothe
sheet. is there any other way

 

[Arvi Laanemets]

Hi

Define dynamic named ranges like:
StockRng=OFFSET(Sheet1!$A$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
SizeRng=OFFSET(Sheet1!$B$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
ColorRng=OFFSET(Sheet1!$C$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
CustomerRng=OFFSET(Sheet1!$D$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)
AmountRng=OFFSET(Sheet1!$E$1,,,COUNTIF(Sheet1!$A:$A,"<>"),1)

(Here I assumed that your data are on Sheet1 in columns A:E, and that there
are no empty rows in table. NB! the COUNTIF must be made on same column for
all ranges used in SUMPRODUCT)

On summary sheet, create data validation lists for conditions.

P.e. Stock, Size, Color and Customer in Range B1:B4
You will have a single cell with formula for total amount in it:
=SUMPRODUCT((StockRng=B1)*(SizeRng=B2)*(ColorRng=B3)*(CustomerRng=B4)*(Amoun
t))

You can enter p.e. all possible sizes into some range (p.e. A5:A20) on
summary sheet. Now you need 3 conditions fixed in range B1:B3. The formula
for cell B5 (total amount), will be:
=SUMPRODUCT((StockRng=B1)*(ColorRng=B2)*(CustomerRng=B3)*(SizeRng=A5)*(Amoun
t))
Copy this formula down, and you have a table for amounts per size for
selected Stock, Color and Customer.

You can create a 2-dimensional table, with sizes and colors as column and
row headers (they are a better choice, because probably they have limeted
number of possible different values), and Stock and Customer as selectable
parameters.

An alternative is to use ODBC query to create a summary table in separate
workbook. You can set the query to be refreshed when worksheet is opened.

 

[Frank Kabel]

You may consider using a pivot table for this