| 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
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)? |
|
#4
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)? |
|
#5
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)? |
|
#6
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)? |
|
#7
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)? |
|
#8
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)? |
|
#9
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)? |
|
#10
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)? |
|
#11
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)? |
|
#12
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)? |
|
#13
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)) |
| 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 | |||
Hybrid Mode
