| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum if Critea 1 and critera 2 are met
Good day,
I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
Any time you have multiple criteria, use SUMPRODUCT():
=SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100)) -- Gary''s Student - gsnu200859 "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
And of course you can omit the double unary minus as you've used * to
multiply. =SUMPRODUCT((A2:A100="Q4")*(C2:C100="prodA")*(F2:F 100)) or =SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100)) -- David Biddulph "Gary''s Student" wrote in message ... Any time you have multiple criteria, use SUMPRODUCT(): =SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100)) -- Gary''s Student - gsnu200859 "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
Thanks that help, now if I want to use the same criteria for getting the
Average of a column that has percentages would I change sum product to Average? Thanks -- Nelson "Gary''s Student" wrote: Any time you have multiple criteria, use SUMPRODUCT(): =SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100)) -- Gary''s Student - gsnu200859 "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
Hi,
In 2007 you would use =SUMIFS(F2:F100,A2:A100,"Q4",C2:C100,"prodA") and =AVERAGEIFS(F2:F100,A2:A100,"Q4",C2:C100,"prodA") In 2003: =AVERAGE(IF((A2:A100="Q4")*(C2:C100="prodA"),F2:F1 00,"")) This formula must be array entered - press Shift+Ctrl+Enter, instead of enter or =SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),F2:F100)/=SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA")) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
In Fun:
And of course you can ommit the parentheses in the final argument of the second formula when you use the double minus: =SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100)) =SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),F2:F100) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "David Biddulph" wrote: And of course you can omit the double unary minus as you've used * to multiply. =SUMPRODUCT((A2:A100="Q4")*(C2:C100="prodA")*(F2:F 100)) or =SUMPRODUCT(--(A2:A100="Q4"),--(C2:C100="prodA"),(F2:F100)) -- David Biddulph "Gary''s Student" wrote in message ... Any time you have multiple criteria, use SUMPRODUCT(): =SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100)) -- Gary''s Student - gsnu200859 "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum if Critea 1 and critera 2 are met
I want to use the same criteria for getting
the Average of a column What version of Excel are you using? This will work in all versions of Excel. Array entered** =AVERAGE(IF((A2:A100="Q4")*(C2:C100="prodA"),F2:F1 00)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Nelson" wrote in message ... Thanks that help, now if I want to use the same criteria for getting the Average of a column that has percentages would I change sum product to Average? Thanks -- Nelson "Gary''s Student" wrote: Any time you have multiple criteria, use SUMPRODUCT(): =SUMPRODUCT(--(A2:A100="Q4")*(C2:C100="prodA")*(F2:F100)) -- Gary''s Student - gsnu200859 "Nelson" wrote: Good day, I have a column A2:A100 that specifies Q1,2,3,4 I have a column C2:C100 that specifies Product (either A or B) in column F2:F100 I have price paid Now I want to create a query that says if A2:A100 = Q1 and C2:C100 = product A then add up all the matching prices from F2:F100 I was using this but I think I am missing something =SUM(IF(((A2:A100)="Q4")*(MATCH(C2:C100,"prodA",0) ),F2::F100)) Any suggestions? Thanks in advance -- Nelson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| SUMIF when using a range with critera | Excel Discussion (Misc queries) | |||
| Average only with specific critera | Excel Discussion (Misc queries) | |||
| How do I sum with two critera? | Excel Discussion (Misc queries) | |||
Linear Mode
