Multiples conditions in an array
 

I have a table with 4 columns. I need to get the sum of column D if certain
conditions are present in each of the 3 columns. I want the sum of column D
if Column A = 6000, Column C = North and Column B = ABC or DEF or MNO

When I use the formula below, the result I am getting is 3500 which is equal
to all the "North". I need the sum to only be 2500. Is the formula below
correct or is there a more appropriate one to use?

Thank you !

SUMPRODUCT((OR(B1:B24="ABC",B1:B24="DEF",B1:B24="M NO"))*(A1:A24=6000)*(C1:C24="NORTH"),D1:D24)

A B C D
1 6000 ABC NORTH 1000
2 6000 DEF NORTH 500
3 6000 GHI EAST 1000
4 6000 JKL WEST 500
5 6000 MNO NORTH 1000
6 6000 PQR SOUTH 500
7 6000 STU NORTH 1000
8 6000 VWX WEST 500
9 6100 ABC NORTH 1000
10 6100 DEF SOUTH 500
11 6100 GHI EAST 1000
12 6100 JKL WEST 500
13 6100 MNO NORTH 1000
14 6100 PQR SOUTH 500
15 6100 STU EAST 1000
16 6100 VWX WEST 500
17 6200 ABC NORTH 1000
18 6200 DEF SOUTH 500
19 6200 GHI EAST 1000
20 6200 JKL WEST 500
21 6200 MNO NORTH 1000
22 6200 PQR SOUTH 500
23 6200 STU EAST 1000
24 6200 VWX WEST 500

Multiples conditions in an array
 

Lea
Try something like this:
SUMPRODUCT((B1:B24={"ABC","DEF","MNO"})*(A1:A24=60 00)*(C1:C24="NORTH")*(D1:D24))

Regards,
Pedro J.

I have a table with 4 columns. I need to get the sum of column D if certain
conditions are present in each of the 3 columns. I want the sum of column D
if Column A = 6000, Column C = North and Column B = ABC or DEF or MNO

When I use the formula below, the result I am getting is 3500 which is equal
to all the "North". I need the sum to only be 2500. Is the formula below
correct or is there a more appropriate one to use?

Thank you !

SUMPRODUCT((OR(B1:B24="ABC",B1:B24="DEF",B1:B24="M NO"))*(A1:A24=6000)*(C1:C24="NORTH"),D1:D24)

A B C D
1 6000 ABC NORTH 1000
2 6000 DEF NORTH 500
3 6000 GHI EAST 1000
4 6000 JKL WEST 500
5 6000 MNO NORTH 1000
6 6000 PQR SOUTH 500
7 6000 STU NORTH 1000
8 6000 VWX WEST 500
9 6100 ABC NORTH 1000
10 6100 DEF SOUTH 500
11 6100 GHI EAST 1000
12 6100 JKL WEST 500
13 6100 MNO NORTH 1000
14 6100 PQR SOUTH 500
15 6100 STU EAST 1000
16 6100 VWX WEST 500
17 6200 ABC NORTH 1000
18 6200 DEF SOUTH 500
19 6200 GHI EAST 1000
20 6200 JKL WEST 500
21 6200 MNO NORTH 1000
22 6200 PQR SOUTH 500
23 6200 STU EAST 1000
24 6200 VWX WEST 500

Multiples conditions in an array
 

Try this:

=SUMPRODUCT((A1:A24=6000)*(B1:B24={"ABC","DEF","MN O"})*(C1:C24="NORTH")*D1:D24)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lea from CA" <Lea from wrote in message
...
I have a table with 4 columns. I need to get the sum of column D if
certain
conditions are present in each of the 3 columns. I want the sum of column
D
if Column A = 6000, Column C = North and Column B = ABC or DEF or MNO

When I use the formula below, the result I am getting is 3500 which is
equal
to all the "North". I need the sum to only be 2500. Is the formula below
correct or is there a more appropriate one to use?

Thank you !

SUMPRODUCT((OR(B1:B24="ABC",B1:B24="DEF",B1:B24="M NO"))*(A1:A24=6000)*(C1:C24="NORTH"),D1:D24)

A B C D
1 6000 ABC NORTH 1000
2 6000 DEF NORTH 500
3 6000 GHI EAST 1000
4 6000 JKL WEST 500
5 6000 MNO NORTH 1000
6 6000 PQR SOUTH 500
7 6000 STU NORTH 1000
8 6000 VWX WEST 500
9 6100 ABC NORTH 1000
10 6100 DEF SOUTH 500
11 6100 GHI EAST 1000
12 6100 JKL WEST 500
13 6100 MNO NORTH 1000
14 6100 PQR SOUTH 500
15 6100 STU EAST 1000
16 6100 VWX WEST 500
17 6200 ABC NORTH 1000
18 6200 DEF SOUTH 500
19 6200 GHI EAST 1000
20 6200 JKL WEST 500
21 6200 MNO NORTH 1000
22 6200 PQR SOUTH 500
23 6200 STU EAST 1000
24 6200 VWX WEST 500

Multiples conditions in an array
 

Thank you! That worked! I saw several threads regarding sumproduct and I
new this is what needed to use...Thanks again!

"Infinitogool" wrote:

Lea
Try something like this:
SUMPRODUCT((B1:B24={"ABC","DEF","MNO"})*(A1:A24=60 00)*(C1:C24="NORTH")*(D1:D24))

Regards,
Pedro J.

I have a table with 4 columns. I need to get the sum of column D if certain
conditions are present in each of the 3 columns. I want the sum of column D
if Column A = 6000, Column C = North and Column B = ABC or DEF or MNO

When I use the formula below, the result I am getting is 3500 which is equal
to all the "North". I need the sum to only be 2500. Is the formula below
correct or is there a more appropriate one to use?

Thank you !

SUMPRODUCT((OR(B1:B24="ABC",B1:B24="DEF",B1:B24="M NO"))*(A1:A24=6000)*(C1:C24="NORTH"),D1:D24)

A B C D
1 6000 ABC NORTH 1000
2 6000 DEF NORTH 500
3 6000 GHI EAST 1000
4 6000 JKL WEST 500
5 6000 MNO NORTH 1000
6 6000 PQR SOUTH 500
7 6000 STU NORTH 1000
8 6000 VWX WEST 500
9 6100 ABC NORTH 1000
10 6100 DEF SOUTH 500
11 6100 GHI EAST 1000
12 6100 JKL WEST 500
13 6100 MNO NORTH 1000
14 6100 PQR SOUTH 500
15 6100 STU EAST 1000
16 6100 VWX WEST 500
17 6200 ABC NORTH 1000
18 6200 DEF SOUTH 500
19 6200 GHI EAST 1000
20 6200 JKL WEST 500
21 6200 MNO NORTH 1000
22 6200 PQR SOUTH 500
23 6200 STU EAST 1000
24 6200 VWX WEST 500