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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Date: Wed, 18 Feb 2004 16:21:13 -0700

Did you try it?

Your function:

    Multiplies two boolean arrays (implicitly coercing the boolean
values to numeric with the * operator). IF() causes elements of the
third array to be placed in a fourth array if the result of the
multiplication was not zero for each element. If the result of the
multiplication was 0, then FALSE is inserted into the fourth array.
Finally, that array is SUMmed.

My function:

    Two boolean arrays are explicitly coerced to numeric via the double
unary minus operators. The first, second and third array are then
multiplied together, yeilding the value in the third array for elements
in which both elements in the first two arrays are TRUE/1, and 0 if
either of the elements in the first two arrays are FALSE/0. The
resulting array is summed.

Much the same thing, but SUMPRODUCT is faster.

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

> Regarding SUMPRODUCT, either I understand it wrong, but what you are
> suggesting is a different function to what I have got by sum(if( doing,
>
> I can not see how SUMPRODUCT will ever do what I need in this case
>
> thanks
>
> tom
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-68FADD.09055718022004@msnews.microsoft.com...
> > 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))

Relevant Pages

  • Re: Arrays: compare fields?
    ... Just use single dimension array for the names to match. ... > Dim myArray() As Variant ... >>the matched rows into a third array and write this third array into ...
    (microsoft.public.excel.programming)
  • Re: How do i change numerics into binary numbers?
    ... Those are needed details. ... We all make mistakes, but here, detailed information is required before ... In addition to that array, you could build another array that indicates ... bytes from the input and store the new bytes in a third array. ...
    (microsoft.public.vb.general.discussion)
  • Re: Frequency question
    ... where 90 is the value in your highest bin. ... In this formula generates an array of 100 TRUE/FALSE ... depending on whether the cells in A1:A100 are greater than B1. ... corresponding element of the third array will equal 1. ...
    (microsoft.public.mac.office.excel)
  • Re: Basic SUM or SUMPRODUCT question
    ... Bob's formula multiplies the first array by the result of the second ... divided by the sum of the third array. ...
    (microsoft.public.excel)
  • Re: Countifs Fx in 07 how in 03?
    ... I said the SUMPRODUCT doesn't take the product ... I have no multiplication, but I still get the sum of the elements, which is ... because each element in the first array is multiplied by the ... Peltier Technical Services, Inc. - http://PeltierTech.com ...
    (microsoft.public.excel.misc)