SUMPRODUCT Help

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

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

 

[Joe User]

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

So it is best to post your messages as follow-ups to the original thread
(pick one). That way, people who want to help you get the benefit of seeing
the context, especially other responses. Moving forward, I suggest that you
keep all related discussion in __this__ thread, since this seems to have the
more complete description of the problem.

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.
[....]
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")
[....]
the answer should be 43% ... but using this equation I
keep coming up w/ 29%.

That formula counts the number of corresponding pairs of cells in V4:X100
and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and
Q4, plus X4 and R4 . In other words, it is equivalent to:

=SUMPRODUCT((V4:X100=2)*(P4100="B 111")
+ (W4:W100=2)*(Q4:Q100="B 111")
+ (X4:X100=2)*(R4:R100="B 111"))

Apparently that is not how you want to count things. But it is unclear how
you do want to count things.

What if there are two or three 2s in V4, W4 and X4 -- that is, in the same
row? Or is that not possible?

What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the
same row? Or is that not possible?

Assuming you want to count one or more 2s in one row and one or more "B
111"s in the same row as one, try:

=SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)>0)
* ((P4100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")>0) )

Note: That formulation seems necessary even if you expect only one 2 and
one "B 111" in the same row, but not necessarily in the same relative column
of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R.

Please confirm (or refute) my assumption -- ``you want to count one or more
2s in one row and one or more "B 111"s in the same row as one``. Someone
else might come up with a more terse way of expressing that formula.

But that assumption seems to contradict the assumption of your divisor,
unless you assume (and ideally ensure) that there is no more than one 2 and
one "B 111" in the same row. If the latter, then you can continue to divide
by COUNTIF(V4:X100,2) to compute percentage.


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

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

Thanks for responding. To clarify for you,

It IS impossible for there to be more than one 2 in a row (they can only
choose each program once, so they can choose 3 different programs, but not
the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other
combinations).

So, it IS impossible for there to be more than one "B 111" in a row as well. Students can choose up to 3 different courses that were beneficial, so each course can only be in a row once.

When using the equation you gave me, the answer still comes out wrong:
=SUMPRODUCT((V4:X100=2)*(P4100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+
(X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking at
V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at V
through X at the same time, find a '2' and then see what rows also said "B
111" in columns P through R. Does that make sense? There can never be more
than one 2 in a row, and there can never be more than one "B 111" in a row --
BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking for.
How many rows have both.

Hopefully that helps you in trying to figure out what equation I'm needing
to use!!!

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

So it is best to post your messages as follow-ups to the original thread
(pick one). That way, people who want to help you get the benefit of seeing
the context, especially other responses. Moving forward, I suggest that you
keep all related discussion in __this__ thread, since this seems to have the
more complete description of the problem.

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.
[....]
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")
[....]
the answer should be 43% ... but using this equation I
keep coming up w/ 29%.

That formula counts the number of corresponding pairs of cells in V4:X100
and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and
Q4, plus X4 and R4 . In other words, it is equivalent to:

=SUMPRODUCT((V4:X100=2)*(P4100="B 111")
+ (W4:W100=2)*(Q4:Q100="B 111")
+ (X4:X100=2)*(R4:R100="B 111"))

Apparently that is not how you want to count things. But it is unclear how
you do want to count things.

What if there are two or three 2s in V4, W4 and X4 -- that is, in the same
row? Or is that not possible?

What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in the
same row? Or is that not possible?

Assuming you want to count one or more 2s in one row and one or more "B
111"s in the same row as one, try:

=SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)>0)
* ((P4100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")>0) )

Note: That formulation seems necessary even if you expect only one 2 and
one "B 111" in the same row, but not necessarily in the same relative column
of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R.

Please confirm (or refute) my assumption -- ``you want to count one or more
2s in one row and one or more "B 111"s in the same row as one``. Someone
else might come up with a more terse way of expressing that formula.

But that assumption seems to contradict the assumption of your divisor,
unless you assume (and ideally ensure) that there is no more than one 2 and
one "B 111" in the same row. If the latter, then you can continue to divide
by COUNTIF(V4:X100,2) to compute percentage.


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

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

.

 

[Joe User]

Thanks for responding. To clarify for you,

It IS impossible for there to be more than one 2 in a row (they can only
choose each program once, so they can choose 3 different programs, but not
the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other
combinations).

So, it IS impossible for there to be more than one "B 111" in a row as
well. Students can choose up to 3 different courses that were beneficial,
so each course can only be in a row once.

When using the equation you gave me, the answer still comes out wrong:
=SUMPRODUCT((V4:X100=2)*(P4100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+
(X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking
at
V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at
V
through X at the same time, find a '2' and then see what rows also said "B
111" in columns P through R. Does that make sense? There can never be more
than one 2 in a row, and there can never be more than one "B 111" in a
row --
BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking
for.
How many rows have both.

Hopefully that helps you in trying to figure out what equation I'm needing
to use!!!

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

So it is best to post your messages as follow-ups to the original thread
(pick one). That way, people who want to help you get the benefit of
seeing
the context, especially other responses. Moving forward, I suggest that
you
keep all related discussion in __this__ thread, since this seems to have
the
more complete description of the problem.

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.
[....]
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")
[....]
the answer should be 43% ... but using this equation I
keep coming up w/ 29%.

That formula counts the number of corresponding pairs of cells in V4:X100
and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and
Q4, plus X4 and R4 . In other words, it is equivalent to:

=SUMPRODUCT((V4:X100=2)*(P4100="B 111")
+ (W4:W100=2)*(Q4:Q100="B 111")
+ (X4:X100=2)*(R4:R100="B 111"))

Apparently that is not how you want to count things. But it is unclear
how
you do want to count things.

What if there are two or three 2s in V4, W4 and X4 -- that is, in the
same
row? Or is that not possible?

What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in
the
same row? Or is that not possible?

Assuming you want to count one or more 2s in one row and one or more "B
111"s in the same row as one, try:

=SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)>0)
* ((P4100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")>0) )

Note: That formulation seems necessary even if you expect only one 2 and
one "B 111" in the same row, but not necessarily in the same relative
column
of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R.

Please confirm (or refute) my assumption -- ``you want to count one or
more
2s in one row and one or more "B 111"s in the same row as one``. Someone
else might come up with a more terse way of expressing that formula.

But that assumption seems to contradict the assumption of your divisor,
unless you assume (and ideally ensure) that there is no more than one 2
and
one "B 111" in the same row. If the latter, then you can continue to
divide
by COUNTIF(V4:X100,2) to compute percentage.


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

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

.

 

[Joe User]

When using the equation you gave me, the answer still comes out wrong:
=SUMPRODUCT((V4:X100=2)*(P4100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+
(X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking
at
V w/ P, W w/ Q, and W w/ R

You misread my response. That is __not__ the formula I was proposing. The
formula above was intended to demonstrate the flaw in the formula that you
were trying to leverage, namely =SUMPRODUCT((V4:X100=2)*(P4:R100="B 111")).
And yes, the flaw is exactly as you (and I) described it.

The alternative formula that I proposed is:

=SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)>0)
* ((P4100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")>0) )

As I explained previously (emphasis added): "That formulation seems
necessary __even _if__ you expect only one 2 and one "B 111" in the same
row, but not necessarily in the same relative column of V:X and P:R, i.e.
not necessarily V and P, or W and Q, or X and R.

Please let me know if that resolves your problem.

[Sorry for the previous contentless response. I pushed the wrong button yet
again <sigh>.]


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

Thanks for responding. To clarify for you,

It IS impossible for there to be more than one 2 in a row (they can only
choose each program once, so they can choose 3 different programs, but not
the same program twice. One row could say V4= 3, W4 = 6, X4= 2 (or other
combinations).

So, it IS impossible for there to be more than one "B 111" in a row as
well. Students can choose up to 3 different courses that were beneficial,
so each course can only be in a row once.

When using the equation you gave me, the answer still comes out wrong:
=SUMPRODUCT((V4:X100=2)*(P4100="B 111")+ (W4:W100=2)*(Q4:Q100="B 111")+
(X4:X100=2)*(R4:R100="B 111")) as I think what it is doing is only looking
at
V w/ P, W w/ Q, and W w/ R when what I need is for the equation to look at
V
through X at the same time, find a '2' and then see what rows also said "B
111" in columns P through R. Does that make sense? There can never be more
than one 2 in a row, and there can never be more than one "B 111" in a
row --
BUT a row can have both a 2 and a B 111 ... and THAT is what I'm looking
for.
How many rows have both.

Hopefully that helps you in trying to figure out what equation I'm needing
to use!!!

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

So it is best to post your messages as follow-ups to the original thread
(pick one). That way, people who want to help you get the benefit of
seeing
the context, especially other responses. Moving forward, I suggest that
you
keep all related discussion in __this__ thread, since this seems to have
the
more complete description of the problem.

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.
[....]
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")
[....]
the answer should be 43% ... but using this equation I
keep coming up w/ 29%.

That formula counts the number of corresponding pairs of cells in V4:X100
and P4:R100 that meet both conditions, for example V4 and P4, plus W4 and
Q4, plus X4 and R4 . In other words, it is equivalent to:

=SUMPRODUCT((V4:X100=2)*(P4100="B 111")
+ (W4:W100=2)*(Q4:Q100="B 111")
+ (X4:X100=2)*(R4:R100="B 111"))

Apparently that is not how you want to count things. But it is unclear
how
you do want to count things.

What if there are two or three 2s in V4, W4 and X4 -- that is, in the
same
row? Or is that not possible?

What if there are two or three "B 111"s in P4, Q4 and R4 -- that is, in
the
same row? Or is that not possible?

Assuming you want to count one or more 2s in one row and one or more "B
111"s in the same row as one, try:

=SUMPRODUCT( ((V4:V100=2)+(W4:W100=2)+(X4:X100=2)>0)
* ((P4100="B 111")+(Q4:Q100="B 111")+(R4:R100="B 111")>0) )

Note: That formulation seems necessary even if you expect only one 2 and
one "B 111" in the same row, but not necessarily in the same relative
column
of V:X and P:R, i.e. not necessarily V and P, or W and Q, or X and R.

Please confirm (or refute) my assumption -- ``you want to count one or
more
2s in one row and one or more "B 111"s in the same row as one``. Someone
else might come up with a more terse way of expressing that formula.

But that assumption seems to contradict the assumption of your divisor,
unless you assume (and ideally ensure) that there is no more than one 2
and
one "B 111" in the same row. If the latter, then you can continue to
divide
by COUNTIF(V4:X100,2) to compute percentage.


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

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

.