Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct
I can create a sumproduct formula that refers to external cells for a
filtering function e.g. =sumproduct(A1:A200=A205:D205)*(B1:B200=1). this should return the sum of those # of rows where there is a 1 in row B and any of 4 different values (which I can switch from text or numbers) in the first 4 cells of row 205. Question is how can I indicate either blanks or nonblanks in one of the 4 reference cells in row 205. In addition, what does the operator "-" do when replacing the asterisk (*) in the sumproduct formula. I know that a plus sign means "or" rather than "and" for the asterisk. does the "-" symbol mean "but not"? So if I want a formula that indicates, for example everyhing with a "apple" in column one but not a "orange" in column 2? |
#2
|
|||
|
|||
"anand" wrote in message ... I can create a sumproduct formula that refers to external cells for a filtering function e.g. =sumproduct(A1:A200=A205:D205)*(B1:B200=1). That should be =SUMPRODUCT((A1:A200=A205:D205)*(B1:B200=1)) this should return the sum of those # of rows where there is a 1 in row B and any of 4 different values (which I can switch from text or numbers) in the first 4 cells of row 205. Question is how can I indicate either blanks or nonblanks in one of the 4 reference cells in row 205. What exactly do you mean? It works with blanks in A1:A200, or in A205:D205 In addition, what does the operator "-" do when replacing the asterisk (*) in the sumproduct formula. I know that a plus sign means "or" rather than "and" for the asterisk. does the "-" symbol mean "but not"? No, the - goes with another, a double unary, which is used to coerce the True/False results of a test are coerced to 1/0 values, for the arithmetic operations. So if I want a formula that indicates, for example everyhing with a "apple" in column one but not a "orange" in column 2? No, that is a simple < =SUMPRODUCT(--(A1:A200="apple),--(B1:B200<"orange")) equivalent to =SUMPRODUCT((A1:A200="apple)*(B1:B200<"orange")) |
#3
|
|||
|
|||
First:
"Question is how can I indicate either blanks or nonblanks in one of the 4 reference cells in row 205." I presume you mean -exclude cells with blanks? In that case =SUMPRODUCT((A1:A200=A205:D205)*(B1:B200=1)*(A205: D205<0)) Second: I think you mean -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html Hope it helped Ola Sandström |
#4
|
|||
|
|||
anand wrote:
I can create a sumproduct formula that refers to external cells for a filtering function e.g. =sumproduct(A1:A200=A205:D205)*(B1:B200=1). this should return the sum of those # of rows where there is a 1 in row B and any of 4 different values (which I can switch from text or numbers) in the first 4 cells of row 205. Question is how can I indicate either blanks or nonblanks in one of the 4 reference cells in row 205. In addition, what does the operator "-" do when replacing the asterisk (*) in the sumproduct formula. I know that a plus sign means "or" rather than "and" for the asterisk. does the "-" symbol mean "but not"? So if I want a formula that indicates, for example everyhing with a "apple" in column one but not a "orange" in column 2? =SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$200,$A$205:$D$205,0)),--($B$1:$B$200=1)) |
#5
|
|||
|
|||
No, what I'm really trying to do is creat a series of external references for
a dozens of sumproduct formula. Then copy the formulas whole and duplicate (with a fixed data block). The sumproduct formulas should give identical results (presuming that I copy over the reference cells also. However, now what I'd want to do is adjust the reference cells so that I'm looking at a different set of conditions. In some cases, I may want to include either a blank, all nonblanks or mabe all. Any idea how to use a reference cell to indicate this? I would assume if I'm using a series of reference cells in a row linked by "or" as this function works, then adding something than indicates blanks, blanks, or all could be done; just not sure how. If used all or non-blanks, this would likey over-ride any selection in rest of reference range i.e. if I have 1,2,3,4 as acceptable conditions and replace with 1,2,3,all, then I would presume that the 1,2,3 become irrelevant. So question remains, is there any symbol that can indicate blanks, non-blanks or all within a reference cell attached to a sumproduct function? "Ola" wrote: First: "Question is how can I indicate either blanks or nonblanks in one of the 4 reference cells in row 205." I presume you mean -exclude cells with blanks? In that case =SUMPRODUCT((A1:A200=A205:D205)*(B1:B200=1)*(A205: D205<0)) Second: I think you mean -- http://www.mcgimpsey.com/excel/formulae/doubleneg.html Hope it helped Ola Sandström |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |