Help w/ SUMPRODUCT

[alh06]

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!

 

[Eduardo]

Hi,

=SUMPRODUCT(--(x4:x100=5),--(y4:y100=5),--(z4:z100=5),--(e4:e100=2))

 

[Dave Peterson]

Check your original post.

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!

 

[Jacob Skaria]

Try
=SUMPRODUCT((X4:Z100=2)*(E4:E100=5))

 

[alh06]

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]

try

=SUMPRODUCT((x4:Y100=5)*(e4:e100=2))

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?

 

[Joe User]

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)*(e4: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)*(e4: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 -----

 

[Joe User]

PS....

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 -----

 

[alh06]

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?

 

[Dave Peterson]

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.

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

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!

 

[Joe User]

I apologize for my 3rd question w/in two days, but every time one thing
works, another thing does not.

See responses to your more-recent thread ("SUMPRODUCT Help") dated 12/2/2009
1:33 PM (PT). As I wrote there, and you must have seen, please do not post
multiple threads on the same topic, especially in the same NG. It
bifurcates attempts to help you, leading to redundancy and misdirection.


----- original message -----

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

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!