hello,

if i have 4 columns like this:
3 3500 2 10
5 2000 4 35
5 4000 5 12
3 1100 1 5
3 5000 0 8
5 6000 0 25
5 7000 8 15

i want to get The sum of column D minus column C if they met 2 criteria: if
it is equal 3 on column A, and between 2000 to 4000 of column B, which
formula should i use? Please help. thanks

Try this:

=SUMPRODUCT(--(A1:A7=3),--(B1:B72000),--(B1:B7<4000),D1:D7-C1:C7)

Based on your sample data the result is 8.


--
Biff
Microsoft Excel MVP


"kim" wrote in message
...
hello,

if i have 4 columns like this:
3 3500 2 10
5 2000 4 35
5 4000 5 12
3 1100 1 5
3 5000 0 8
5 6000 0 25
5 7000 8 15

i want to get The sum of column D minus column C if they met 2 criteria:
if
it is equal 3 on column A, and between 2000 to 4000 of column B, which
formula should i use? Please help. thanks

it works. thanks so much

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A7=3),--(B1:B72000),--(B1:B7<4000),D1:D7-C1:C7)

Based on your sample data the result is 8.


--
Biff
Microsoft Excel MVP


"kim" wrote in message
...
hello,

if i have 4 columns like this:
3 3500 2 10
5 2000 4 35
5 4000 5 12
3 1100 1 5
3 5000 0 8
5 6000 0 25
5 7000 8 15

i want to get The sum of column D minus column C if they met 2 criteria:
if
it is equal 3 on column A, and between 2000 to 4000 of column B, which
formula should i use? Please help. thanks

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"kim" wrote in message
...

it works. thanks so much

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A1:A7=3),--(B1:B72000),--(B1:B7<4000),D1:D7-C1:C7)

Based on your sample data the result is 8.


--
Biff
Microsoft Excel MVP


"kim" wrote in message
...
hello,

if i have 4 columns like this:
3 3500 2 10
5 2000 4 35
5 4000 5 12
3 1100 1 5
3 5000 0 8
5 6000 0 25
5 7000 8 15

i want to get The sum of column D minus column C if they met 2
criteria:
if
it is equal 3 on column A, and between 2000 to 4000 of column B, which
formula should i use? Please help. thanks