Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))))) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))))) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Discussion (Misc queries) | |||
sumproduct | Excel Worksheet Functions | |||
how to use sumproduct | Excel Worksheet Functions |