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?



--
Jayen

=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter


On Nov 21, 3:14*pm, Jayen wrote:
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?

--
Jayen

oops type

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))
*B1:B60


On Nov 21, 3:47*pm, muddan madhu wrote:
=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter

On Nov 21, 3:14*pm, Jayen wrote:



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?

--
Jayen- Hide quoted text -

- Show quoted text -

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:

(D1:D60=230)+(D1:D60=540)

--(ISNUMBER(MATCH(D1:D160,{230,540},0)))

--
Biff
Microsoft Excel MVP


"muddan madhu" wrote in message
...
oops type

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))
*B1:B60


On Nov 21, 3:47 pm, muddan madhu wrote:
=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter

On Nov 21, 3:14 pm, Jayen wrote:



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?

--
Jayen- Hide quoted text -

- Show quoted text -

Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))

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

=SUMPRODUCT(--(((D1:D4=230)+(D1:D4=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


"muddan madhu" wrote in message
...
oops type

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))
*B1:B60


On Nov 21, 3:47 pm, muddan madhu wrote:
=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter

On Nov 21, 3:14 pm, Jayen wrote:



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?

--
Jayen- Hide quoted text -

- Show quoted text -

=SUMPRODUCT(--(((D1:D4=230)+(D1:D4=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:

(D1:D4=230)+(D1:D4=540)

Will be either 1 or 0.

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

Or, you could use the multiplication syntax:

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


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))

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

=SUMPRODUCT(--(((D1:D4=230)+(D1:D4=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


"muddan madhu" wrote in message
...
oops type

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))
*B1:B60


On Nov 21, 3:47 pm, muddan madhu wrote:
=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter

On Nov 21, 3:14 pm, Jayen wrote:



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?

--
Jayen- Hide quoted text -

- Show quoted text -

Hi Biff,

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

cheers,
Shane

"T. Valko" wrote:

=SUMPRODUCT(--(((D1:D4=230)+(D1:D4=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:

(D1:D4=230)+(D1:D4=540)

Will be either 1 or 0.

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

Or, you could use the multiplication syntax:

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


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
Hi,

Careful with this idea

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))

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

=SUMPRODUCT(--(((D1:D4=230)+(D1:D4=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


"muddan madhu" wrote in message
...
oops type

=SUMPRODUCT(--(OR(D1:D60=230,D1:D60=540)*(K1:K60=973)*(L1:L60="0 24")))
*B1:B60


On Nov 21, 3:47 pm, muddan madhu wrote:
=SUMPRODUCT(--(OR(D1:D60=230,D17:D18=540)*(K1:K60=973)*(L1:L60=" 024")))
*B17:B18

use ctrl + shift + enter

On Nov 21, 3:14 pm, Jayen wrote:



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?

--
Jayen- Hide quoted text -

- Show quoted text -