SUMPRODUCT...with text values within array
 

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

SUMPRODUCT...with text values within array
 

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

SUMPRODUCT...with text values within array
 

=SUMIF(C1:C10,"1")

--
HTH

Bob Phillips

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

"F. Lawrence Kulchar" wrote in
message ...
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

SUMPRODUCT...with text values within array
 

BTW you could have used

=SUMPRODUCT(--(C1:C101),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" wrote in
message ...
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

SUMPRODUCT...with text values within array
 

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

SUMPRODUCT...with text values within array
 

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)

"F. Lawrence Kulchar" wrote in
message ...
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

SUMPRODUCT...with text values within array
 

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

SUMPRODUCT...with text values within array
 

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

SUMPRODUCT...with text values within array
 

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" wrote in message
...
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

SUMPRODUCT...with text values within array
 

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

"Bob Phillips" wrote:

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" wrote in message
...
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

SUMPRODUCT...with text values within array
 

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)

"Mike Jerakis" wrote in message
...
Thanks, Bob. I think that will work. Assuming of course you meant
"Application"

"Bob Phillips" wrote:


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" wrote in message
...
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