|
sumproduct function
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 function
=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 |
sumproduct function
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 function
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 - |
sumproduct function
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 function
=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 - |
sumproduct function
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 - |
| All times are GMT +1. The time now is 07:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com