Re: SUM(IF..... in Visual basic

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 09:05:57 -0700

It's certainly possible, but would have a horrendous disadvantage in
both speed and memory. In general, using VBA is much slower than using
XL's built-in compiled and optimized functions.

There is a speed advantage to using SUMPRODUCT() rather than SUM(IF()),
though (Note: SUMPRODUCT is not array-entered):

 =SUMPRODUCT(--(DataStore!$I$4:$I$39=$E$24),
--(DataStore!$E$4:$E$39=$F$9), DataStore!J$4:J$39)

In article <40328723$0$21298$cc9e4d1f@news.dial.pipex.com>,
 "Tom" <reply@list.com> wrote:

> I have a simple sumif that does two conditional test:
>
>
>
> =SUM(IF(('DataStore'!$I$4:$I$39=$E24)*('DataStore'!$E$4:$E$39=$F$9),'DataSto
> re'!J$4:J$39))
>
>
>
> Is it possible to turn this into a generic user defined function :
>
> SumMatchInArray(DataStoreToSum,
> Condtion1Case,Condtion1Test,Condtion2Case,Condtion2Test.Condtion3Case,Condti
> on3Test,etc)
>
>
>
> If so does this have a memory advantage?
>
>
>
> Can some one point me in the direction of an example... I'm new to VB and
> excel programming.

Relevant Pages

  • Re: Namespaces in assembly language
    ... > increase the speed on GHz boxes with huge arrays of memory available. ... > my observation that there's no speed advantage either way on current ... is that they don't usually write such large applications. ...
    (alt.lang.asm)
  • RE: DSUM vs. SUMPRODUCT
    ... I've heard that the DSumcommand requires considerably less memory. ... "matelot" wrote: ... I've been using SUMPRODUCT for all ...
    (microsoft.public.excel.programming)
  • Re: difference between xml dataset and sql/access?
    ... With 1000 records I'd pretty much flip a coin on it. ... Any speed advantage would be negligible, ... You will use more memory with XML, but that's probably only about 10 megs or so and on modern systems that is almost nothing. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Building a Pivot Table where column headings will change
    ... A pivottables takes more memory than a sumproduct for the same data and ... it is also much more sensetive to the chance ... pivottables but can be automatic with SUMPRODUCT. ...
    (microsoft.public.excel.programming)
  • Re: creating a ram drive in csharp
    ... Memory mapped files are loaded into memory but otherwise behave as ordinary ... Thus you get the speed advantage of having the file in memory without ...
    (microsoft.public.dotnet.languages.csharp)