SUM USING MULTIPLE CRITERIA
 

I want to add data in one column after evaluating criteria in 2 separate
columns. I have tried arrays and nested functions but can't figure it out.
Any ideas? Please see example below.


Cust Prod Code Loc On Order
B W TNC 300
A Y VBN 2000
A Y VBN 500
A Z AOK 1000

Calculate sum of "On Order" based on 2 criteria - "Prod Code" and "Loc"
Example: Criteria: Prod Code="Y" and Loc="VBN". On Order=2500.

SUM USING MULTIPLE CRITERIA
 

Column B = Prod Code
Column C = Loc
Column D = Sum range

Try something like this:
=SUMPRODUCT(($B$2:$B$1000="Y")*($C$2:$C$1000="VBN" )*($D$2:$D$1000))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"brownmre" wrote:

I want to add data in one column after evaluating criteria in 2 separate
columns. I have tried arrays and nested functions but can't figure it out.
Any ideas? Please see example below.


Cust Prod Code Loc On Order
B W TNC 300
A Y VBN 2000
A Y VBN 500
A Z AOK 1000

Calculate sum of "On Order" based on 2 criteria - "Prod Code" and "Loc"
Example: Criteria: Prod Code="Y" and Loc="VBN". On Order=2500.

SUM USING MULTIPLE CRITERIA
 

AMAZING...IT WORKS. I thought I'd never get here. Thanks!

"JBeaucaire" wrote:

Column B = Prod Code
Column C = Loc
Column D = Sum range

Try something like this:
=SUMPRODUCT(($B$2:$B$1000="Y")*($C$2:$C$1000="VBN" )*($D$2:$D$1000))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"brownmre" wrote:

I want to add data in one column after evaluating criteria in 2 separate
columns. I have tried arrays and nested functions but can't figure it out.
Any ideas? Please see example below.


Cust Prod Code Loc On Order
B W TNC 300
A Y VBN 2000
A Y VBN 500
A Z AOK 1000

Calculate sum of "On Order" based on 2 criteria - "Prod Code" and "Loc"
Example: Criteria: Prod Code="Y" and Loc="VBN". On Order=2500.

SUM USING MULTIPLE CRITERIA
 

Awesome. Glad to help.
--

"Actually, I *am* a rocket scientist." -- JB
Your feedback is appreciated, click YES if this post helped you.


"brownmre" wrote:
AMAZING...IT WORKS. I thought I'd never get here. Thanks!

SUM USING MULTIPLE CRITERIA
 

"brownmre" wrote:

AMAZING...IT WORKS. I thought I'd never get here. Thanks!

"JBeaucaire" wrote:

Column B = Prod Code
Column C = Loc
Column D = Sum range

Try something like this:
=SUMPRODUCT(($B$2:$B$1000="Y")*($C$2:$C$1000="VBN" )*($D$2:$D$1000))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"brownmre" wrote:

I want to add data in one column after evaluating criteria in 2 separate
columns. I have tried arrays and nested functions but can't figure it out.
Any ideas? Please see example below.


Cust Prod Code Loc On Order
B W TNC 300
A Y VBN 2000
A Y VBN 500
A Z AOK 1000

Calculate sum of "On Order" based on 2 criteria - "Prod Code" and "Loc"
Example: Criteria: Prod Code="Y" and Loc="VBN". On Order=2500.