Sumproduct with If

  • Thread starter Thread starter Angie
  • Start date Start date
A

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)?
 
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:
 
sorry typo mysteak

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



"Marcelo" escreveu:
 
Angie said:
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)?
Click to expand...

=SUMPRODUCT((A1:A100)*(B1:B100)*(C1:C100=51))
 
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 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.]
 
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:
 
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"))
 
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?
 
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:
 
Angie said:
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?
Click to expand...

=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND("7",C1:C100)))))
 
Glenn said:
=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND("7",C1:C100)))))
Click to expand...


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


=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND(E1,C1:C100)))))
 
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 said:
Try
Assuming your data is on a8:c13

=sumproduct(--(c8:c13)*(a8:b13))
Click to expand...
"Angie" escreveu:
Click to expand...
Click to expand...
 
Glenn said:
Or, you can replace the "7" with a cell reference.

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

Or you could eliminate a function call

=SUMPRODUCT(A1:A100*B1:B100*ISNUMBER(FIND(E1,C1:C100)))
 
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 said:
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)?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top