This is great as I have previously been using array formulas, but I think
this may make things less susceptible to error.

But I have a related question:
How might you use this formula inside a VBA function? I know through code
that I could step through all the data, but that takes a long time, Excel
seems to do this formula very quickly. And when you might have alot, time is
critical.

The formula
= Application.WorksheetFunction.SumProduct((rng1.Val ue = chk1) * (rng2.Value
= chk2), rng3)

does not seem to work, any suggestions.

"Tom Ogilvy" wrote:

but why does it NOT work w/o a 3rd "bogus" array??

Sumproduct only works on arrays of numbers.

In your case, You need to convert the first argument to numbers. By
creating two arrays of booleans and multiplying them you do that. Bob
showed you a way to do it by using two negation operators without a dummy
column.

this also works
=SUMPRODUCT(1*(C2:C71),(C2:C7))

but purportedly the double negation is faster.

--
Regards,
Tom Ogilvy


"F. Lawrence Kulchar" wrote:

YES...i know that...

but why does it NOT work w/o a 3rd "bogus" array??

"Die_Another_Day" wrote:

You could test if the value is a number like this:
=SUMPRODUCT((ISNUMBER(C2:C7))*(C2:C71),(C2:C7))

Charles

F. Lawrence Kulchar wrote:
Assume the following are in the cells, and I wish to add all values greater
than 1.

A B C
1 10
2 8
3 5
4 -2
5 abc
6 4
7 12

The following formula works:

=SUMPRODUCT((C2:C71)*(C2:C7<100),(C2:C7))

HOWEVER, if I remove the logical expression (C2:C7<100), then my formula
does NOT work because in cell C5 is a text value..

How can I use the sumproduct formula without inputting the unnecessary
array...C2:C7<100??

Thamk you,

FLKulchar