Sumproduct with If

[Angie]

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

 

[Marcelo]

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:

 

[StumpedAgain]

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

 

[Marcelo]

sorry typo mysteak

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



"Marcelo" escreveu:

 

[Glenn]

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

 

[Angie]

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

 

[David Biddulph]

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

 

[Marcelo]

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:

 

[T. Valko]

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

 

[Angie]

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]

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:

 

[Glenn]

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

 

[Glenn]

=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,C1:C100)))))

 

[Marcelo]

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

Try
Assuming your data is on a8:c13

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

"Angie" escreveu:

 

[Harlan Grove]

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:C100)))

 

[Glenn]

Or you could eliminate a function call

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

Good point. Thanks!

 

[David Biddulph]

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

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

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

 

[Marcelo]

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

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

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