Home |
Search |
Today's Posts |
#1
|
|||
|
|||
multiple criteria then sum
I am trying to figure out how to sum a column based on 2 criteria being true.
criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
#2
|
|||
|
|||
Hi
One option is to use SUMPRODUCT. There is some guidance he http://www.contextures.com/xlFunctio...tml#SumProduct In your example, something like: =SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10) When using this function, each range must contain the same number of cells and cannot be full columns. Andy. "Kit" wrote in message ... I am trying to figure out how to sum a column based on 2 criteria being true. criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
#3
|
|||
|
|||
What if you have two criteria and want to sum the same column?
I tried =SUMPRODUCT((A2:A10=3)*(B2:B10=2),B2:B10) and got a VALUE error "Andy" wrote: Hi One option is to use SUMPRODUCT. There is some guidance he http://www.contextures.com/xlFunctio...tml#SumProduct In your example, something like: =SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10) When using this function, each range must contain the same number of cells and cannot be full columns. Andy. "Kit" wrote in message ... I am trying to figure out how to sum a column based on 2 criteria being true. criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
#4
|
|||
|
|||
Hi
I've just tried it and it worked for me!! Make sure that your 'numbers' are actually numbers and not text. Andy. "DG" wrote in message ... What if you have two criteria and want to sum the same column? I tried =SUMPRODUCT((A2:A10=3)*(B2:B10=2),B2:B10) and got a VALUE error "Andy" wrote: Hi One option is to use SUMPRODUCT. There is some guidance he http://www.contextures.com/xlFunctio...tml#SumProduct In your example, something like: =SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10) When using this function, each range must contain the same number of cells and cannot be full columns. Andy. "Kit" wrote in message ... I am trying to figure out how to sum a column based on 2 criteria being true. criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
#5
|
|||
|
|||
Many thanks Andy! I tried it and it works nicely. I will check out the link
as well.... Regards, Kit "Andy" wrote: Hi One option is to use SUMPRODUCT. There is some guidance he http://www.contextures.com/xlFunctio...tml#SumProduct In your example, something like: =SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10) When using this function, each range must contain the same number of cells and cannot be full columns. Andy. "Kit" wrote in message ... I am trying to figure out how to sum a column based on 2 criteria being true. criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
#6
|
|||
|
|||
Thanks for the feedback!!
Andy. "Kit" wrote in message ... Many thanks Andy! I tried it and it works nicely. I will check out the link as well.... Regards, Kit "Andy" wrote: Hi One option is to use SUMPRODUCT. There is some guidance he http://www.contextures.com/xlFunctio...tml#SumProduct In your example, something like: =SUMPRODUCT((A2:A10=3)*(B2:B10=2),C2:C10) When using this function, each range must contain the same number of cells and cannot be full columns. Andy. "Kit" wrote in message ... I am trying to figure out how to sum a column based on 2 criteria being true. criteria1 criteria2 volume 3 2 100 2 2 500 2 1 600 3 2 200 For example, I want the sum for volume when criteria 1 = 3 and criteria 2 = 2 simultanously. In this example, volume would equal 300. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract multiple records matching criteria from list | Excel Worksheet Functions | |||
Multiple Criteria (add or subtract) | Excel Discussion (Misc queries) | |||
Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
Counting "rows", i.e. simultaneous criteria for multiple cells | Excel Worksheet Functions |