sumproduct and conditionals
 

hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using
=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

apparently it only works as
=Or(Sumproduct(),Sumproduct())


"Gixxer_J_97" wrote:

hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using
=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

J,

OR can be done using Addition:

=SUMPRODUCT(((MID(Codes,1,2)="DI")+(MID(Codes,1,2) ="GB"))*(Types="Sample"))

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using

=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

Hi Bernie,

I had tried that before, and the result is 2 - i was looking to have a 0 or
1 to use as a True/False

but it would work if i were to specify the result of this logical 0

thanks!

J


"Bernie Deitrick" wrote:

J,

OR can be done using Addition:

=SUMPRODUCT(((MID(Codes,1,2)="DI")+(MID(Codes,1,2) ="GB"))*(Types="Sample"))

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using

=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

the result is actually the count of instances of DI/Sample and GB/Sample
- it'll actually come in useful for another section of my program.

thanks again!

J

"Bernie Deitrick" wrote:

J,

OR can be done using Addition:

=SUMPRODUCT(((MID(Codes,1,2)="DI")+(MID(Codes,1,2) ="GB"))*(Types="Sample"))

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using

=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

=SUMPRODUCT((MID(codes,1,2)={"DI","GB"})*(Types="S ample"))

--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using

=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

=N(SUMPRODUCT((MID(codes,1,2)={"DI","GB"})*(Types= "Sample"))0)

--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
Hi Bernie,

I had tried that before, and the result is 2 - i was looking to have a 0
or
1 to use as a True/False

but it would work if i were to specify the result of this logical 0

thanks!

J


"Bernie Deitrick" wrote:

J,

OR can be done using Addition:


=SUMPRODUCT(((MID(Codes,1,2)="DI")+(MID(Codes,1,2) ="GB"))*(Types="Sample"))

HTH,
Bernie
MS Excel MVP


"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using


=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

That works!

Thanks Bob!

J

"Bob Phillips" wrote:

=SUMPRODUCT((MID(codes,1,2)={"DI","GB"})*(Types="S ample"))

--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using

=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J

sumproduct and conditionals
 

Now you confuse me, it gives the same result as Bernie's?

--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
That works!

Thanks Bob!

J

"Bob Phillips" wrote:

=SUMPRODUCT((MID(codes,1,2)={"DI","GB"})*(Types="S ample"))

--
HTH

Bob Phillips

"Gixxer_J_97" wrote in message
...
hi all

i am trying to combine to sumproduct statements

they are

=Sumproduct((mid(codes,1,2)="DI")*(Types="Sample") )
which works
and
=Sumproduct((mid(codes,1,2)="GB")*(Types="Sample") )
which also works

i tried using


=SUMPRODUCT((OR(MID(Codes,1,2)="DI",MID(Codes,1,2) ="GB"))*(Types="Sample"))
but that returns a 0, not the 1 it should (or i thought it should)

any suggestions?

tia

J