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


"Pete_UK" wrote:

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)=1 1)*(YEAR(B1:B40000)=2007)*(C1:C40000="Y")*(D1:D400 00))

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

On Nov 21, 6:57 pm, Jim May wrote:
=SUMPRODUCT((VisionPaymtsPosted!$A$5:$A$12000=Carr yOverRecon!$C8)*(VisionPa-ymtsPosted!$E$4:$AC$4=CarryOverRecon!W$5)*(VisionP aymtsPosted!$E$5:$AC$1200-0))

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" wrote:
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

On Nov 21, 2:47 pm, Jim May wrote:
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- Hide quoted text -

- Show quoted text -