sumif or sumproduct

[kim]

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

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A7=3),--(B1:B7>2000),--(B1:B7<4000),D17-C1:C7)

Based on your sample data the result is 8.

 

[kim]

it works. thanks so much

Try this:

=SUMPRODUCT(--(A1:A7=3),--(B1:B7>2000),--(B1:B7<4000),D17-C1:C7)

Based on your sample data the result is 8.

 

[T. Valko]

You're welcome. Thanks for the feedback!