Conditional Sum Product

I want to get the sumproduct of two rows of numbers based on an IF condition
in a related row. The formula would look like this:
=Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise Multiplier =1
So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then the
product would be b2*b3*1. Any ideas?

=SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ben010" <[email protected]> wrote in message
news:[email protected]...
> I want to get the sumproduct of two rows of numbers based on an IF
condition
> in a related row. The formula would look like this:
> =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise Multiplier
=1
> So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then the
> product would be b2*b3*1. Any ideas?

Bob,

I was reading thru some of the posts and saw your response...I have a
question. I use sumproduct alot, but I do not/have not used the (--
What does this do exactly?

Thanks,

Hans

Bob Phillips wrote:
> =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > I want to get the sumproduct of two rows of numbers based on an IF
> condition
> > in a related row. The formula would look like this:
> > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise Multiplier
> =1
> > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then the
> > product would be b2*b3*1. Any ideas?

I have written a page on that. See
http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> I was reading thru some of the posts and saw your response...I have a
> question. I use sumproduct alot, but I do not/have not used the (--
> What does this do exactly?
>
> Thanks,
>
> Hans
>
> Bob Phillips wrote:
> > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ben010" <[email protected]> wrote in message
> > news:[email protected]...
> > > I want to get the sumproduct of two rows of numbers based on an IF
> > condition
> > > in a related row. The formula would look like this:
> > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
Multiplier
> > =1
> > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then
the
> > > product would be b2*b3*1. Any ideas?
>

Bob--
Thanks. My original query was probably not clear. If A1:Z1 is "Yes" then I
want the cell to compute to your answer times a multiplier. If "No" I want
the cell to compute to exactly your answer (multiplier =1).

Ben

"Bob Phillips" wrote:

> =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > I want to get the sumproduct of two rows of numbers based on an IF
> condition
> > in a related row. The formula would look like this:
> > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise Multiplier
> =1
> > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then the
> > product would be b2*b3*1. Any ideas?
>
>
>

What multiplier?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ben010" <[email protected]> wrote in message
news:[email protected]...
> Bob--
> Thanks. My original query was probably not clear. If A1:Z1 is "Yes" then
I
> want the cell to compute to your answer times a multiplier. If "No" I
want
> the cell to compute to exactly your answer (multiplier =1).
>
> Ben
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ben010" <[email protected]> wrote in message
> > news:[email protected]...
> > > I want to get the sumproduct of two rows of numbers based on an IF
> > condition
> > > in a related row. The formula would look like this:
> > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
Multiplier
> > =1
> > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then
the
> > > product would be b2*b3*1. Any ideas?
> >
> >
> >

Let me show you the first part of my array:
A B C D X Y
1 Yes No Yes Yes 1.5 1
2 6 3 2 2
3 2 4 6 3

A1:D3 is my array. X and Y are the multipliers. The calculation for this
would be :
6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
corresponding product multiplies by 1.5. If A1:D1 is "No" then it multiplies
by 1.



"Bob Phillips" wrote:

> What multiplier?
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > Bob--
> > Thanks. My original query was probably not clear. If A1:Z1 is "Yes" then
> I
> > want the cell to compute to your answer times a multiplier. If "No" I
> want
> > the cell to compute to exactly your answer (multiplier =1).
> >
> > Ben
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "Ben010" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I want to get the sumproduct of two rows of numbers based on an IF
> > > condition
> > > > in a related row. The formula would look like this:
> > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
> Multiplier
> > > =1
> > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then
> the
> > > > product would be b2*b3*1. Any ideas?
> > >
> > >
> > >
>
>
>

=SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*({1.5;1})*(A2:Z2)*(A3:Z3)
)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ben010" <[email protected]> wrote in message
news:[email protected]...
> Let me show you the first part of my array:
> A B C D X Y
> 1 Yes No Yes Yes 1.5 1
> 2 6 3 2 2
> 3 2 4 6 3
>
> A1:D3 is my array. X and Y are the multipliers. The calculation for this
> would be :
> 6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
> corresponding product multiplies by 1.5. If A1:D1 is "No" then it
multiplies
> by 1.
>
>
>
> "Bob Phillips" wrote:
>
> > What multiplier?
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ben010" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bob--
> > > Thanks. My original query was probably not clear. If A1:Z1 is "Yes"
then
> > I
> > > want the cell to compute to your answer times a multiplier. If "No"
I
> > want
> > > the cell to compute to exactly your answer (multiplier =1).
> > >
> > > Ben
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace xxxx in the email address with gmail if mailing direct)
> > > >
> > > > "Ben010" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I want to get the sumproduct of two rows of numbers based on an IF
> > > > condition
> > > > > in a related row. The formula would look like this:
> > > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
> > Multiplier
> > > > =1
> > > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes"
then
> > the
> > > > > product would be b2*b3*1. Any ideas?
> > > >
> > > >
> > > >
> >
> >
> >

Bob--
Thanks. I appreciate the help.

"Bob Phillips" wrote:

> =SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*({1.5;1})*(A2:Z2)*(A3:Z3)
> )
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > Let me show you the first part of my array:
> > A B C D X Y
> > 1 Yes No Yes Yes 1.5 1
> > 2 6 3 2 2
> > 3 2 4 6 3
> >
> > A1:D3 is my array. X and Y are the multipliers. The calculation for this
> > would be :
> > 6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
> > corresponding product multiplies by 1.5. If A1:D1 is "No" then it
> multiplies
> > by 1.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > What multiplier?
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "Ben010" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Bob--
> > > > Thanks. My original query was probably not clear. If A1:Z1 is "Yes"
> then
> > > I
> > > > want the cell to compute to your answer times a multiplier. If "No"
> I
> > > want
> > > > the cell to compute to exactly your answer (multiplier =1).
> > > >
> > > > Ben
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > >
> > > > > "Ben010" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I want to get the sumproduct of two rows of numbers based on an IF
> > > > > condition
> > > > > > in a related row. The formula would look like this:
> > > > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
> > > Multiplier
> > > > > =1
> > > > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes"
> then
> > > the
> > > > > > product would be b2*b3*1. Any ideas?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Bob....Thanks a bunch! Bookmarked your site!

Hans


Bob Phillips wrote:
> I have written a page on that. See
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
> explanation.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> >
> > I was reading thru some of the posts and saw your response...I have a
> > question. I use sumproduct alot, but I do not/have not used the (--
> > What does this do exactly?
> >
> > Thanks,
> >
> > Hans
> >
> > Bob Phillips wrote:
> > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "Ben010" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I want to get the sumproduct of two rows of numbers based on an IF
> > > condition
> > > > in a related row. The formula would look like this:
> > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
> Multiplier
> > > =1
> > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes" then
> the
> > > > product would be b2*b3*1. Any ideas?
> >

Bob--
How can I make the {1.5;1} numbers variables? That is I want to reference a
cell rather than make them numbers, such as {v1;v2}

Ben

"Bob Phillips" wrote:

> =SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*({1.5;1})*(A2:Z2)*(A3:Z3)
> )
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > Let me show you the first part of my array:
> > A B C D X Y
> > 1 Yes No Yes Yes 1.5 1
> > 2 6 3 2 2
> > 3 2 4 6 3
> >
> > A1:D3 is my array. X and Y are the multipliers. The calculation for this
> > would be :
> > 6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
> > corresponding product multiplies by 1.5. If A1:D1 is "No" then it
> multiplies
> > by 1.
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > What multiplier?
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "Ben010" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Bob--
> > > > Thanks. My original query was probably not clear. If A1:Z1 is "Yes"
> then
> > > I
> > > > want the cell to compute to your answer times a multiplier. If "No"
> I
> > > want
> > > > the cell to compute to exactly your answer (multiplier =1).
> > > >
> > > > Ben
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > >
> > > > > "Ben010" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I want to get the sumproduct of two rows of numbers based on an IF
> > > > > condition
> > > > > > in a related row. The formula would look like this:
> > > > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes", otherwise
> > > Multiplier
> > > > > =1
> > > > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes"
> then
> > > the
> > > > > > product would be b2*b3*1. Any ideas?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Yeah, if contiguous

=SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*(V1:V2)*(A2:Z2)*(A3:Z3))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Ben010" <[email protected]> wrote in message
news:[email protected]...
> Bob--
> How can I make the {1.5;1} numbers variables? That is I want to reference
a
> cell rather than make them numbers, such as {v1;v2}
>
> Ben
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*({1.5;1})*(A2:Z2)*(A3:Z3)
> > )
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > "Ben010" <[email protected]> wrote in message
> > news:[email protected]...
> > > Let me show you the first part of my array:
> > > A B C D X Y
> > > 1 Yes No Yes Yes 1.5 1
> > > 2 6 3 2 2
> > > 3 2 4 6 3
> > >
> > > A1:D3 is my array. X and Y are the multipliers. The calculation for
this
> > > would be :
> > > 6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
> > > corresponding product multiplies by 1.5. If A1:D1 is "No" then it
> > multiplies
> > > by 1.
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > What multiplier?
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (replace xxxx in the email address with gmail if mailing direct)
> > > >
> > > > "Ben010" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Bob--
> > > > > Thanks. My original query was probably not clear. If A1:Z1 is
"Yes"
> > then
> > > > I
> > > > > want the cell to compute to your answer times a multiplier. If
"No"
> > I
> > > > want
> > > > > the cell to compute to exactly your answer (multiplier =1).
> > > > >
> > > > > Ben
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > > > > >
> > > > > > --
> > > > > >
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > > >
> > > > > > "Ben010" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > I want to get the sumproduct of two rows of numbers based on
an IF
> > > > > > condition
> > > > > > > in a related row. The formula would look like this:
> > > > > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes",
otherwise
> > > > Multiplier
> > > > > > =1
> > > > > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes"
> > then
> > > > the
> > > > > > > product would be b2*b3*1. Any ideas?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Nice. Thanks!

"Bob Phillips" wrote:

> Yeah, if contiguous
>
> =SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*(V1:V2)*(A2:Z2)*(A3:Z3))
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> "Ben010" <[email protected]> wrote in message
> news:[email protected]...
> > Bob--
> > How can I make the {1.5;1} numbers variables? That is I want to reference
> a
> > cell rather than make them numbers, such as {v1;v2}
> >
> > Ben
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUMPRODUCT((ISNUMBER(SEARCH({"Yes";"No"},A1:Z1)))*({1.5;1})*(A2:Z2)*(A3:Z3)
> > > )
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace xxxx in the email address with gmail if mailing direct)
> > >
> > > "Ben010" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Let me show you the first part of my array:
> > > > A B C D X Y
> > > > 1 Yes No Yes Yes 1.5 1
> > > > 2 6 3 2 2
> > > > 3 2 4 6 3
> > > >
> > > > A1:D3 is my array. X and Y are the multipliers. The calculation for
> this
> > > > would be :
> > > > 6*2*1.5+ 3*4*1+ 2*6*1.5+ 2*3*1.5. Thus, if A1:D1 is "Yes" then the
> > > > corresponding product multiplies by 1.5. If A1:D1 is "No" then it
> > > multiplies
> > > > by 1.
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > What multiplier?
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > >
> > > > > "Ben010" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Bob--
> > > > > > Thanks. My original query was probably not clear. If A1:Z1 is
> "Yes"
> > > then
> > > > > I
> > > > > > want the cell to compute to your answer times a multiplier. If
> "No"
> > > I
> > > > > want
> > > > > > the cell to compute to exactly your answer (multiplier =1).
> > > > > >
> > > > > > Ben
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > =SUMPRODUCT(--(A1:Z1="Yes"),A2:Z2,A3:Z3)
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > (replace xxxx in the email address with gmail if mailing direct)
> > > > > > >
> > > > > > > "Ben010" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > I want to get the sumproduct of two rows of numbers based on
> an IF
> > > > > > > condition
> > > > > > > > in a related row. The formula would look like this:
> > > > > > > > =Sumproduct(a2:z2, a3:z3)* Multiplier if a1:z1="Yes",
> otherwise
> > > > > Multiplier
> > > > > > > =1
> > > > > > > > So if a1="Yes" then the product would be a2*a3*M; if b1<>"Yes"
> > > then
> > > > > the
> > > > > > > > product would be b2*b3*1. Any ideas?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>