sumif or sumproduct

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kim - 13 Mar 2008 05:39 GMT

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

Reply to this Message

T. Valko - 13 Mar 2008 05:57 GMT

Try this:

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

Based on your sample data the result is 8.

Signature

Biff
Microsoft Excel MVP

> hello,
>
[quoted text clipped - 11 lines]
> it is equal 3 on column A, and between 2000 to 4000 of column B, which
> formula should i use? Please help. thanks

Reply to this Message

kim - 13 Mar 2008 19:15 GMT

it works. thanks so much

> Try this:
>
[quoted text clipped - 17 lines]
> > it is equal 3 on column A, and between 2000 to 4000 of column B, which
> > formula should i use? Please help. thanks

Reply to this Message

T. Valko - 13 Mar 2008 19:42 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> it works. thanks so much
>
[quoted text clipped - 20 lines]
>> > it is equal 3 on column A, and between 2000 to 4000 of column B, which
>> > formula should i use? Please help. thanks

Reply to this Message