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)?
but only if the value in column C meets a certain criteria (like = 51)?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
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:
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:
S
StumpedAgain
=IF(C1=51,SUM(A:B),"")
M
Marcelo
sorry typo mysteak
=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"Marcelo" escreveu:
=sumproduct(--(c8:c13=51)*(a8:b13))
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"Marcelo" escreveu:
G
Glenn
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)?
=SUMPRODUCT((A1:A100)*(B1:B100)*(C1:C100=51))
A
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
with wildcards?
For example: sumproduct columns A and B, but only when the value in column C
begins with 7
D
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.]
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.]
M
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:
=sumproduct(--(left(c8:c13,1)="7")*(a8:a13))
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"Angie" escreveu:
T
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"))
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"))
A
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?
really need is a wildcard that looks for values in column C that CONTAIN 7,
rather than start with 7. Any ideas?
M
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:
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:
G
Glenn
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?
=SUMPRODUCT(A1:A100*B1:B100*(NOT(ISERROR(FIND("7",C1:C100)))))
G
Glenn
Glenn said:=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)))))
M
Marcelo
Yes David, I repost the correct with the =51
thanks
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"David Biddulph" escreveu:
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))"Angie" escreveu:
H
Harlan Grove
Glenn said: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)))
G
Glenn
Harlan said:Or you could eliminate a function call
=SUMPRODUCT(A1:A100*B1:B100*ISNUMBER(FIND(E1,C1:C100)))
Good point. Thanks!
M
Marcelo
Yes, you are right
My mistake
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo
"David Biddulph" escreveu:
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)?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.