sumproduct function

[Jayen]

My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either of two
values i.e. (--([file1] sheet1!$D$1:$D$60="230" or “540â€). Can someone
please help with how to do this correctly? I have tried using OR, * but
this does not work. Is there any other function I could use if I cannot do
this with the SUMPRODUCT function?

 

[muddan madhu]

=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter

 

[muddan madhu]

oops type

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))
*B1:B60

=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter

My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either of two
values i.e.   (--([file1] sheet1!$D$1:$D$60="230" or “540”).  Can someone
please help with how to do this correctly?   I have tried using OR, *but
this does not work.  Is there any other function I could use if I cannot do
this with the SUMPRODUCT function?

- Show quoted text -

 

[T. Valko]

You can't use OR in SUMPRODUCT like that. OR returns a single boolean for
the entire array.

You have to use something like this:

(D160=230)+(D160=540)

--(ISNUMBER(MATCH(D1160,{230,540},0)))

--
Biff
Microsoft Excel MVP


oops type

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))
*B1:B60

=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter

My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either of
two
values i.e. (--([file1] sheet1!$D$1:$D$60="230" or “540”). Can someone
please help with how to do this correctly? I have tried using OR, * but
this does not work. Is there any other function I could use if I cannot
do
this with the SUMPRODUCT function?

- Show quoted text -

 

[Shane Devenshire]

Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))

If it returns the correct results it is just LUCK! The correct formula is:

=SUMPRODUCT(--(((D14=230)+(D14=540))>0),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Adjust the references to match your needs.

This is the SUMPRODUCT version of the OR function.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

oops type

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))
*B1:B60

=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter

My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either of
two
values i.e. (--([file1] sheet1!$D$1:$D$60="230" or “540â€). Can someone
please help with how to do this correctly? I have tried using OR, * but
this does not work. Is there any other function I could use if I cannot
do
this with the SUMPRODUCT function?

- Show quoted text -

 

[T. Valko]

=SUMPRODUCT(--(((D14=230)+(D14=540))>0),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Since you're testing the same range for the "or" condition you don't need to
test for >0. The result of:

(D14=230)+(D14=540)

Will be either 1 or 0.

=SUMPRODUCT((D14=230)+(D14=540),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Or, you could use the multiplication syntax:

=SUMPRODUCT((D14={230,540})*(K1:K4=973)*(L1:L4="024")*B1:B4)


--
Biff
Microsoft Excel MVP

Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))

If it returns the correct results it is just LUCK! The correct formula
is:

=SUMPRODUCT(--(((D14=230)+(D14=540))>0),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Adjust the references to match your needs.

This is the SUMPRODUCT version of the OR function.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

oops type

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))
*B1:B60

=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter




My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either
of
two
values i.e. (--([file1] sheet1!$D$1:$D$60="230" or "540"). Can
someone
please help with how to do this correctly? I have tried using OR, *
but
this does not work. Is there any other function I could use if I
cannot
do
this with the SUMPRODUCT function?

 

[Shane Devenshire]

Hi Biff,

So try. FYI I was refering to Muddan's post not yours.

cheers,
Shane

=SUMPRODUCT(--(((D14=230)+(D14=540))>0),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Since you're testing the same range for the "or" condition you don't need to
test for >0. The result of:

(D14=230)+(D14=540)

Will be either 1 or 0.

=SUMPRODUCT((D14=230)+(D14=540),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Or, you could use the multiplication syntax:

=SUMPRODUCT((D14={230,540})*(K1:K4=973)*(L1:L4="024")*B1:B4)


--
Biff
Microsoft Excel MVP

Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))

If it returns the correct results it is just LUCK! The correct formula
is:

=SUMPRODUCT(--(((D14=230)+(D14=540))>0),--(K1:K4=973),--(L1:L4="024"),B1:B4)

Adjust the references to match your needs.

This is the SUMPRODUCT version of the OR function.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

oops type

=SUMPRODUCT(--(OR(D160=230,D160=540)*(K1:K60=973)*(L1:L60="024")))
*B1:B60


=SUMPRODUCT(--(OR(D160=230,D1718=540)*(K1:K60=973)*(L1:L60="024")))
*B17:B18

use ctrl + shift + enter




My formula, which works fine, is
=SUMPRODUCT(--([file1]sheet1!$D$1:$D$60="230"),
--([file1]sheet1!$K$1:$K$60="973"), --([file1]sheet1!$L$1:$L$60="024"),
[file1]sheet1!$B$1:$B$60)

My problem is that for the first criteria, I want to check for either
of
two
values i.e. (--([file1] sheet1!$D$1:$D$60="230" or "540"). Can
someone
please help with how to do this correctly? I have tried using OR, *
but
this does not work. Is there any other function I could use if I
cannot
do
this with the SUMPRODUCT function?