If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help w/ SUMPRODUCT
I had great feedback when I was trying to figure out an equation for teh
following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#2
|
|||
|
|||
Help w/ SUMPRODUCT
Hi,
=SUMPRODUCT(--(x4:x100=5),--(y4:y100=5),--(z4:z100=5),--(e4:e100=2)) "alh06" wrote: I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#3
|
|||
|
|||
Help w/ SUMPRODUCT
Check your original post.
alh06 wrote: I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! -- Dave Peterson |
#4
|
|||
|
|||
Help w/ SUMPRODUCT
Try
=SUMPRODUCT((X4:Z100=2)*(E4:E100=5)) -- Jacob "alh06" wrote: I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#5
|
|||
|
|||
Help w/ SUMPRODUCT
Unfortunately that does not work - when I do that it tries to find people who
answered as '2' in all 3 columns ... and then brings back a 0%. When I said I'm looking at 3 columns -- I meant someone can only choose '2' once w/in those 3 columns. So for one person, if they chose '2' it will only be show '2' within all 3 columns: one person may have '2' in X while another may have '2' in W -- which is why I need to search that entire range as a whole. Writing the formula out this way is looking for a person who answered '2' in all three columns, and it is impossible. I'm looking for people who answered '2' w/in the range of all 3 columns. (is that making sense?) Any other ideas? "Eduardo" wrote: Hi, =SUMPRODUCT(--(x4:x100=5),--(y4:y100=5),--(z4:z100=5),--(e4:e100=2)) |
#6
|
|||
|
|||
Help w/ SUMPRODUCT
try
=SUMPRODUCT((x4:Y100=5)*(e4:e100=2)) "alh06" wrote: Unfortunately that does not work - when I do that it tries to find people who answered as '2' in all 3 columns ... and then brings back a 0%. When I said I'm looking at 3 columns -- I meant someone can only choose '2' once w/in those 3 columns. So for one person, if they chose '2' it will only be show '2' within all 3 columns: one person may have '2' in X while another may have '2' in W -- which is why I need to search that entire range as a whole. Writing the formula out this way is looking for a person who answered '2' in all three columns, and it is impossible. I'm looking for people who answered '2' w/in the range of all 3 columns. (is that making sense?) Any other ideas? "Eduardo" wrote: Hi, =SUMPRODUCT(--(x4:x100=5),--(y4:y100=5),--(z4:z100=5),--(e4:e100=2)) |
#7
|
|||
|
|||
Help w/ SUMPRODUCT
"alh06" wrote:
Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) You say you want a percentage, but that formula only returns a count. For a percentage, you would need to divide that expression by something, perhaps COUNT(E4:E100) or COUNTA(E4:E100). See the Help pages for COUNT and COUNTA to understand the difference. However, I infer that that formula works for you. So I will assume you are merely interested in a count. BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. It is unclear what "people in 3 columns who identified as 2" means, and it is unclear how you want to correlate the match-up in 3 columns (X, Y, Z) with the matching condition in 1 column (E). If you want to count the number of rows in which there is a 2 in __all__ of columns X, Y and Z as well as a 5 in column E: =sumproduct((x4:x100=2)*(y4:y100=2)*(z4:z100=2)*(e 4:e100=5)) If you want to count the number of rows in which there is a 2 in __at_least_one__ of columns X, Y and Z as well as a 5 in column E: =sumproduct(((x4:x100=2)+(y4:y100=2)+(z4:z100=2)0 )*(e4:e100=5)) If you want to count the number __cells__ in each row which there is a 2 in columns X, Y or Z as well as a 5 in column E: =sumproduct((x4:z100=2)*(e4:e100=5)) The latter is the same as: =sumproduct((x4:x100=2)*(e4:e100=5)+(y4:y100=2)*(e 4:e100=5)+(z4:z100=2)*(e4:e100=5)) which is not the same as the second formula above. It will double-account for the rows in which there are two or three cells in X, Y and Z that have 2. Which of the above formulas give the result that you want? If none, what's different about your interpretation of the question? ----- original message ----- "alh06" wrote in message ... I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#8
|
|||
|
|||
Help w/ SUMPRODUCT
PS....
I wrote: You say you want a percentage, but that formula only returns a count. For a percentage, you would need to divide that expression by something, perhaps COUNT(E4:E100) or COUNTA(E4:E100). In another thread, you wrote: "Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF because I'm only looking to find the percentage of the number of "2"s that answered". That depends on which of the various interpretations matches your intent, if any. To wit.... If you want to count the number of rows in which there is a 2 in __all__ of columns X, Y and Z as well as a 5 in column E, divide by: =sumproduct((x4:x100=2)*(y4:y100=2)*(z4:z100=2)) If you want to count the number of rows in which there is a 2 in __at_least_one__ of columns X, Y and Z as well as a 5 in column E, divide by: =sumproduct(((x4:x100=2)+(y4:y100=2)+(z4:z100=2)0 )) If you want to count the number of __cells__ in each row in which there is a 2 in columns X, Y or Z as well as a 5 in column E, divide by: =countif(x4:z100,2) ----- original message ----- "Joe User" joeu2004 wrote in message ... "alh06" wrote: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) You say you want a percentage, but that formula only returns a count. For a percentage, you would need to divide that expression by something, perhaps COUNT(E4:E100) or COUNTA(E4:E100). See the Help pages for COUNT and COUNTA to understand the difference. However, I infer that that formula works for you. So I will assume you are merely interested in a count. BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. It is unclear what "people in 3 columns who identified as 2" means, and it is unclear how you want to correlate the match-up in 3 columns (X, Y, Z) with the matching condition in 1 column (E). If you want to count the number of rows in which there is a 2 in __all__ of columns X, Y and Z as well as a 5 in column E: =sumproduct((x4:x100=2)*(y4:y100=2)*(z4:z100=2)*(e 4:e100=5)) If you want to count the number of rows in which there is a 2 in __at_least_one__ of columns X, Y and Z as well as a 5 in column E: =sumproduct(((x4:x100=2)+(y4:y100=2)+(z4:z100=2)0 )*(e4:e100=5)) If you want to count the number __cells__ in each row which there is a 2 in columns X, Y or Z as well as a 5 in column E: =sumproduct((x4:z100=2)*(e4:e100=5)) The latter is the same as: =sumproduct((x4:x100=2)*(e4:e100=5)+(y4:y100=2)*(e 4:e100=5)+(z4:z100=2)*(e4:e100=5)) which is not the same as the second formula above. It will double-account for the rows in which there are two or three cells in X, Y and Z that have 2. Which of the above formulas give the result that you want? If none, what's different about your interpretation of the question? ----- original message ----- "alh06" wrote in message ... I had great feedback when I was trying to figure out an equation for teh following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#9
|
|||
|
|||
Help w/ SUMPRODUCT
I apologize for my 3rd question w/in two days, but every time one thing
works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? In every row there can ONLY be ONE 2 and there can ONLY be ONE class 'x'. Students can put the '2' in V, W, OR X (not in V AND WAND X) and they can choose to put class 'x' in P, Q, OR R. I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually, 7 rows have a '2' in V:X and of those 7 rows, 3 had class 'B 111' in P:R meaning 3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber "alh06" wrote: I had great feedback when I was trying to figure out an equation for the following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! |
#10
|
|||
|
|||
Help w/ SUMPRODUCT
This won't address your question directly.
Have you thought about using helper cells in additional columns that return an indicator for each criteria that you want? Then you could use an =sumproduct() against those simplified fields. In fact, you may be able to create a formula in a single cell (for each row) that evaluates to true/false and use =countif()'s to find your counts. Depending on how big your data is, the =countif()'s and helper indicator formulas may make your workbook recalculate faster (as well as being easier to understand and modify). If you don't like seeing that additional column, just hide it. alh06 wrote: I apologize for my 3rd question w/in two days, but every time one thing works, another thing does not. In our survey- we are trying to find data in regard to our students (who are in different programs) and what classes are beneficial. Our students can identify themselves in up to 3 different programs out of 6 total programs (coded 1 - 6): (Columns V, W, X) and then can identify up to 3 different beneficial classes (Columns P, Q, R). Each row is a different student. The question is: How many students who identified as '2' also said class 'x' is beneficial? In every row there can ONLY be ONE 2 and there can ONLY be ONE class 'x'. Students can put the '2' in V, W, OR X (not in V AND WAND X) and they can choose to put class 'x' in P, Q, OR R. I have similar questions that were answered using SUMPRODUCT: =SUMPRODUCT((V4:X100=2)*(L4:L100=1))/COUNTIF(V4:X100, "2") (a different question finding OF the students who identified as 'entrepreneur [2]' for program, also said they 'disagreed [1]' w/ a statement) but for some reason, the same equation is not working for looking at the multiple columns for beneficial classes when I'm trying to find OF the 'entrepreneur [2]' students, who thought 'B 111' was beneficial?: =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111"))/COUNTIF(V4:X100, "2") Is it because I'm looking at multiple columns for both values? I've entered in this equation ... and it is bringing up the wrong percentage. When doing the math manually, 7 rows have a '2' in V:X and of those 7 rows, 3 had class 'B 111' in P:R meaning 3 out of the 7 total entrepreneur students surveyed said B 111 was beneficial -- the answer should be 43% ... but using this equation I keep coming up w/ 29%. Is there a different equation to use in this instance when there are multiple column ranges for both values? Thanks! Amber "alh06" wrote: I had great feedback when I was trying to figure out an equation for the following example: Of the people in column 'A' who identified as a "2", what percentage also identified as a "5" in column 'B'? -- And I received this equation from more than one person. =sumproduct(--(a2:a99=2),--(b2:b99=5)) BUT, I am actually trying to find all the people in 3 columns who identified as a "2" (lets say columns X,Y, and Z, rows 4 thru 100) who also identified as a "5" in column E, rows 4:100. When I only do one column, it works -- =sumproduct(--(W4:W100=2),--(E4:E100=5)) but when I add other columns, it does not. How do I put that into the equation? I've tried a few different things, but none are working. I get VALUE errors, NAME errors, and plain old "you did something very wrong" errors. I don't know how to do multiple columns and I don't know entirely where anything goes in regard to parenthesis and funny dashes. Thank you very much! -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|