SumProduct with more than 1 selection
 

Hi all,
I am using the following formular which works fine.

The Z... are named ranges of columns.

=SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt)

But If I want to select a number of additional selections to Zinvest in
the same column
e.g. A, B, C
I seem to be getting a problem.


Tried this which resulted in Zero.

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)


Also tried this which resulted in #VALUE

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt)


Any Suggestions?
Many Thanks
TW

SumProduct with more than 1 selection
 

),--(Zinvest={"A","b","c"}),

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Hi all,
I am using the following formular which works fine.

The Z... are named ranges of columns.

=SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt)

But If I want to select a number of additional selections to Zinvest in
the same column
e.g. A, B, C
I seem to be getting a problem.


Tried this which resulted in Zero.

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)


Also tried this which resulted in #VALUE

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt)


Any Suggestions?
Many Thanks
TW

SumProduct with more than 1 selection
 

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest={"A","B","C"}),Zamt)


--
HTH

Bob Phillips

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

wrote in message
ups.com...
Hi all,
I am using the following formular which works fine.

The Z... are named ranges of columns.

=SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt)

But If I want to select a number of additional selections to Zinvest in
the same column
e.g. A, B, C
I seem to be getting a problem.


Tried this which resulted in Zero.


=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Z
invest="C"),Zamt)


Also tried this which resulted in #VALUE


=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B")
,--(Zinvest="C")),Zamt)


Any Suggestions?
Many Thanks
TW

SumProduct with more than 1 selection
 

TW,

In SUMPRODUCT formulas, you can use + as an analog to OR:

Your idea:

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)

Could also be

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),((Zinvest="A")+(Zinvest="B")+(Zinves t="C")),Zamt)

Just an alternative to the array version, and one which I find easier to remember....

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
Hi all,
I am using the following formular which works fine.

The Z... are named ranges of columns.

=SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt)

But If I want to select a number of additional selections to Zinvest in
the same column
e.g. A, B, C
I seem to be getting a problem.


Tried this which resulted in Zero.

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)


Also tried this which resulted in #VALUE

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt)


Any Suggestions?
Many Thanks
TW

SumProduct with more than 1 selection
 

Hi all,
Thanks for the advise.

Using the array method still came up with an error message #VALUE if I
use more than one array.
But works fine for invividual letters.

So tried Bernie's method and... YES!! this works great.

Thanks again
TW



Bernie Deitrick wrote:
TW,

In SUMPRODUCT formulas, you can use + as an analog to OR:

Your idea:

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)

Could also be

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),((Zinvest="A")+(Zinvest="B")+(Zinves t="C")),Zamt)

Just an alternative to the array version, and one which I find easier to remember....

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
Hi all,
I am using the following formular which works fine.

The Z... are named ranges of columns.

=SUMPRODUCT(--(Zmth="APR"),--(Zbuscode=18),--(Zinvest="A"),Zamt)

But If I want to select a number of additional selections to Zinvest in
the same column
e.g. A, B, C
I seem to be getting a problem.


Tried this which resulted in Zero.

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C"),Zamt)


Also tried this which resulted in #VALUE

=SUMPRODUCT(--(Zmth=B2),--(Zbuscode=18),--OR(--(Zinvest="A"),--(Zinvest="B"),--(Zinvest="C")),Zamt)


Any Suggestions?
Many Thanks
TW