SUM(IF..... in Visual basic

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

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.



thanks



tom
 
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)
 
thanks for the advice regarding VB,

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
 
Tom said:
I can not see how SUMPRODUCT will ever do what I need in this case
Click to expand...

The SUMPRODUCT formula below does *exactly* what the array entered SUMIF
formula below that does. How could you say such a thing without even
having tried it???

Alan Beban
 
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.
 
Back
Top