Sumproduct() - Next best alternative

[Guest]

Since understanding and using the sumproduct() function I use it extensively
in the design of some rather sophisticated spreadsheets. With several sheets
(in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data -
with amaybe 3 or 5 columns of the sumproduct() function in each row. It is
the availability of "multi-criteria" (which I need) that draws me to using
the Sumproduct.

The recalculation (using the tools options, calculation, automatic) is
terrible so I have set it to Manual, but anyway it is taking 1 0r 2 minutes
of "calculating" before the system is available to continue. Am I misusing
the function or is there an alternative that I could use and get the pay-back
of less calc time?

Tks in advance,

Jim May

 

[Dave Peterson]

You may want to look at Pivottables. They may be able to the same summaries
based on your multi-criteria rules.

 

[Stephen]

Since understanding and using the sumproduct() function I use it
extensively
in the design of some rather sophisticated spreadsheets. With several
sheets
(in the WB) I utilize the Auto-Filter on the 4,000 to 5000 rows of data -
with amaybe 3 or 5 columns of the sumproduct() function in each row. It
is
the availability of "multi-criteria" (which I need) that draws me to using
the Sumproduct.

The recalculation (using the tools options, calculation, automatic) is
terrible so I have set it to Manual, but anyway it is taking 1 0r 2
minutes
of "calculating" before the system is available to continue. Am I
misusing
the function or is there an alternative that I could use and get the
pay-back
of less calc time?

Tks in advance,

Jim May

Obviously 1000 similar formulas will take about 1000 times as long to
calculate as one formula, so making each as quick as possible is fruitful if
you need a lot of formulas. I have found two approaches that significantly
reduce computation time, which may or may not be applicable in your
situation.

The first is to keep the ranges as short as possible. In my situation, I had
data covering a one year period, with dates in column A in ascending order,
and I was summarising by month. My original formulas had ranges covering the
whole year. I added a few new formulas on a new sheet to find the first and
last lines for each month, and used the results of these to define named
ranges for each month. My SUMPRODUCT formulas then had ranges on average
only 1/12 as long.

The second is to look at the formulas that are used many times and see if
there is any part of the calculation that literally is repeated in each. For
example, originally in one of my SUMPRODUCT conditions I had something like
(A1:A10000=MAX(Sheet2!$Z$1:$Z$5)). Copying this formula 1000 times meant
that Excel was evaluating MAX(Sheet2!$Z$1:$Z$5) 1000 times. By doing this
bit in another cell and using its result in the SUMPRODUCT condition, it is
only evaluated once.

 

[Pete_UK]

One approach I have used to speed things up is to convert SUMPRODUCT
and SUM(IF(... array formulae to SUMIF, which is very much quicker.
Essentially you need to use other helper columns to join the multi-
criteria columns together into one (and preferably fix the values),
which can then be used with SUMIF.

Hope this helps.

Pete

 

[Guest]

=SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=CarryOverRecon!$C8)*(VisionPaymtsPosted!$E$4:$AC$4=CarryOverRecon!W$5)*(VisionPaymtsPosted!$E$5:$AC$12000))

Here is an example from one sheet where there this formula is in 26 other
columns and 80 rows. How could I incorporate this into the Sumif() function?

 

[Pete_UK]

Jim,

I was describing a situation where you might have several 1-D arrays
of the same size, eg:

=SUMPRODUCT((A1:A40000="Pete")*(MONTH(B1:B40000)=11)*(YEAR(B1:B40000)=2007)*(C1:C40000="Y")*(D140000))

Columns A, B and C can be joined in a way to match the criteria
required, say in column E, and then a SUMIF can replace the formula
above.

I'm not sure how you would apply this to your case, where you seem to
have a 2-D table.

Pete

 

[Guest]

Thanks Pete, In your example what would be the content of Column E?
and also Col F with the Sumif(), maybe =Sumif(E1:E40000,???,D140000)

 

[Pete_UK]

E1 could have a formula like this:

=A1&TEXT(B1,"mm")&TEXT(B1,"yy")&C1

and copied down (preferably with the values fixed as well if the data
is not likely to change).

Then if you have G1 for a name, H1 for a date and I1 for a letter, you
could use this in J1:

=SUMIF(E1:E40000,G1&TEXT(H1,"mm")&TEXT(H1,"yy")&I1,D140000)

The same function used to set up the combination in column E is used
in the middle parameter of the SUMIF - often it would just be a
straight concatenation, but here I wanted to pick out monthly data
from a particular year.

Hope this helps.