SUMPRODUCT...with text values within array

[F. Lawrence Kulchar]

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:C7>1)*(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

 

[Die_Another_Day]

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

Charles

 

[Bob Phillips]

=SUMIF(C1:C10,">1")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bob Phillips]

BTW you could have used

=SUMPRODUCT(--(C1:C10>1),C1:C10)

but as I shoed, SP is not necessary here.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[F. Lawrence Kulchar]

YES...i know that...

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

 

[Bob Phillips]

Not very polite to respond like that when someone tries to help.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tom Ogilvy]

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:C7>1),(C2:C7))

but purportedly the double negation is faster.

 

[Mike Jerakis]

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.Value = chk1) * (rng2.Value
= chk2), rng3)

does not seem to work, any suggestions.

 

[Bob Phillips]

Activesheet.Evaluate("SumProduct((rng1.Value=chk1)*(rng2.Value=chk2),rng3)"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Mike Jerakis]

Thanks, Bob. I think that will work. Assuming of course you meant
"Application"

 

[Bob Phillips]

No, I meant Activesheet. What I didn't mean was to keep the .Value

Activesheet.Evaluate("SumProduct((rng1=chk1)*(rng2=chk2),rng3)")

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)