Sumproduct with If
 

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

=IF(C1=51,SUM(A:B),"")

--
-SA


"Angie" wrote:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Angie wrote:
I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?



=SUMPRODUCT((A1:A100)*(B1:B100)*(C1:C100=51))

Sumproduct with If
 

Thank you Marcelo. That solved that issue. Is there also a way to do this
with wildcards?
For example: sumproduct columns A and B, but only when the value in column C
begins with 7



"Marcelo" wrote:

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Marcelo may have intended to say not =sumproduct(--(c8:c13)*(a8:b13))
but either =sumproduct((c8:c13=51)*(a8:a13)*(b8:b13)) or
=sumproduct(--(c8:c13=51),a8:a13,b8:b13) ?

[You don't need the double unary minus if you've already got an arithmetic
operator such as the * for multiply.]
--
David Biddulph

"Marcelo" wrote in message
...
Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and
B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Hi, if my understand is correct you can use

=sumproduct(--(left(c8:c13,1)="7")*(a8:a13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

Thank you Marcelo. That solved that issue. Is there also a way to do this
with wildcards?
For example: sumproduct columns A and B, but only when the value in column C
begins with 7



"Marcelo" wrote:

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Is there also a way to do this with wildcards?

No. Wildcards can't be used directly in SUMPRODUCT and, in general,
wildcards can only be used on text.

Try it like this:

=SUMPRODUCT(A1:A10,B1:B10,--(LEFT(C1:C10)="7"))

--
Biff
Microsoft Excel MVP


"Angie" wrote in message
...
Thank you Marcelo. That solved that issue. Is there also a way to do
this
with wildcards?
For example: sumproduct columns A and B, but only when the value in column
C
begins with 7



"Marcelo" wrote:

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A
and B,
but only if the value in column C meets a certain criteria (like =
51)?

Sumproduct with If
 

Your are correct for that example. I should have been more specific. What I
really need is a wildcard that looks for values in column C that CONTAIN 7,
rather than start with 7. Any ideas?


"Marcelo" wrote:

Hi, if my understand is correct you can use

=sumproduct(--(left(c8:c13,1)="7")*(a8:a13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

Thank you Marcelo. That solved that issue. Is there also a way to do this
with wildcards?
For example: sumproduct columns A and B, but only when the value in column C
begins with 7



"Marcelo" wrote:

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

ok one suggestion

use on column D as an auxilar column
=if(iserror(find(7,c8)),0,1)

and

=sumproduct(--(d8:d13=1)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

Your are correct for that example. I should have been more specific. What I
really need is a wildcard that looks for values in column C that CONTAIN 7,
rather than start with 7. Any ideas?


"Marcelo" wrote:

Hi, if my understand is correct you can use

=sumproduct(--(left(c8:c13,1)="7")*(a8:a13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

Thank you Marcelo. That solved that issue. Is there also a way to do this
with wildcards?
For example: sumproduct columns A and B, but only when the value in column C
begins with 7



"Marcelo" wrote:

sorry typo mysteak

=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Marcelo" escreveu:

Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Angie wrote:
Your are correct for that example. I should have been more specific. What I
really need is a wildcard that looks for values in column C that CONTAIN 7,
rather than start with 7. Any ideas?



=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND("7", C1:C100)))))

Sumproduct with If
 

Glenn wrote:
Angie wrote:
Your are correct for that example. I should have been more specific.
What I really need is a wildcard that looks for values in column C
that CONTAIN 7, rather than start with 7. Any ideas?



=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND("7", C1:C100)))))


Or, you can replace the "7" with a cell reference.


=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND(E1,C 1:C100)))))

Sumproduct with If
 

Yes David, I repost the correct with the =51

thanks
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David Biddulph" escreveu:

Marcelo may have intended to say not =sumproduct(--(c8:c13)*(a8:b13))
but either =sumproduct((c8:c13=51)*(a8:a13)*(b8:b13)) or
=sumproduct(--(c8:c13=51),a8:a13,b8:b13) ?

[You don't need the double unary minus if you've already got an arithmetic
operator such as the * for multiply.]
--
David Biddulph

"Marcelo" wrote in message
...
Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A and
B,
but only if the value in column C meets a certain criteria (like = 51)?

Sumproduct with If
 

Glenn wrote...
....
Or, you can replace the "7" with a cell reference.

=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND(E1, C1:C100)))))

Or you could eliminate a function call

=SUMPRODUCT(A1:A100*B1:B100*ISNUMBER(FIND(E1,C1:C1 00)))

Sumproduct with If
 

Harlan Grove wrote:
Glenn wrote...
...
Or, you can replace the "7" with a cell reference.

=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND(E1,C 1:C100)))))

Or you could eliminate a function call

=SUMPRODUCT(A1:A100*B1:B100*ISNUMBER(FIND(E1,C1:C1 00)))


Good point. Thanks!

Sumproduct with If
 

Yes, but did you try your formula with ...*(a8:b13)) ?

Did that give the product of columns A and B? I think not.
--
David Biddulph

"Marcelo" wrote in message
...
Yes David, I repost the correct with the =51

thanks
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David Biddulph" escreveu:

Marcelo may have intended to say not =sumproduct(--(c8:c13)*(a8:b13))
but either =sumproduct((c8:c13=51)*(a8:a13)*(b8:b13)) or
=sumproduct(--(c8:c13=51),a8:a13,b8:b13) ?

[You don't need the double unary minus if you've already got an
arithmetic
operator such as the * for multiply.]
--
David Biddulph

"Marcelo" wrote in message
...
Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A
and
B,
but only if the value in column C meets a certain criteria (like =
51)?

Sumproduct with If
 

Yes, you are right

My mistake

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David Biddulph" escreveu:

Yes, but did you try your formula with ...*(a8:b13)) ?

Did that give the product of columns A and B? I think not.
--
David Biddulph

"Marcelo" wrote in message
...
Yes David, I repost the correct with the =51

thanks
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David Biddulph" escreveu:

Marcelo may have intended to say not =sumproduct(--(c8:c13)*(a8:b13))
but either =sumproduct((c8:c13=51)*(a8:a13)*(b8:b13)) or
=sumproduct(--(c8:c13=51),a8:a13,b8:b13) ?

[You don't need the double unary minus if you've already got an
arithmetic
operator such as the * for multiply.]
--
David Biddulph

"Marcelo" wrote in message
...
Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))

"Angie" escreveu:

I hope someone can help me. How can I sum the products of columns A
and
B,
but only if the value in column C meets a certain criteria (like =
51)?