Help on a conditional sum formula - multiple criteria

[pirdop]

Hi,
any idea how I can manage a conditional sum with multiple criteria with formulas?

SUMIF - apparently support just one condition
DSUM - supports multiple criteria but just one property per criteria

the X <> b won't work in this case. I have to explicitly specify the properties as the possible values for X are 25 and it should return TRUE just in 3 cases.

I need to check for 4 conditions if they are TRUE go with the Sum!
The problem is that the 2 of these conditions would return TRUE if more than one property is met:

for example:

if color = red Or yellow Or blue it should return TRUE



thanks

P.S.

 

[p45cal]

Use a free service such as box.net to share your file.

hi again guys and sorry for getting to you so late.

hmmm UI wanted to attach the excel file here, but apparently this option doesn't exist?
I don't have administrative right s on this PC so can't install the excel2html plugin.

any ideas

 

[Baghern]

or a bit shorter:
=SUMPRODUCT((A2:A17={"yellow","blue","red"})*(C2:C17< B2:B17)*(D2:D17="big")*E2:E17)<?xml:namespace prefix = b2 /><b2:b17)*(d2:d17="big")*e2:e17)></b2:b17)*(d2:d17="big")*e2:e17)>

Thanks for this and other replies regarding this subject.

For the above though, the shortening of the forumlae using {xx,yx,zy} Does not work for me, is there a reason for this?

I also noticed on my 2nd computer I need a -- infront of each criteria for it to work as per the formulaes here.

http://www.mrexcel.com/forum/showthread.php?p=2384555&highlight=true+sumif#post2384555

Its there a setting on different computers I'm not aware off?


Thanks,

Bag

 

[Aladin Akyurek]

Thanks for this and other replies regarding this subject.

For the above though, the shortening of the forumlae using {xx,yx,zy} Does not work for me, is there a reason for this?

I also noticed on my 2nd computer I need a -- infront of each criteria for it to work as per the formulaes here.

http://www.mrexcel.com/forum/showthread.php?p=2384555&highlight=true+sumif#post2384555

Its there a setting on different computers I'm not aware off?


Thanks,

Bag

=SUMPRODUCT(
    (A2:A17={"yellow","blue","red"})*
    (C2:C17< B2:B17)*
    (D2:D17="big")*
    E2:E17)

is equivalent to:

=SUMPRODUCT(
    --ISNUMBER(MATCH(A2:A17,{"yellow","blue","red"},0)),
    --(C2:C17< B2:B17),
    --(D2:D17="big"),
    E2:E17)

The latter is probably a tad faster.

Another option is:

Control+shift+enter, not just enter...

=SUM(
   IF(ISNUMBER(MATCH(A2:A17,{"yellow","blue","red"},0)),
   IF(C2:C17< B2:B17,
   IF(D2:D17="big",
    E2:E17))))

 

[Baghern]

Thanks,

I'm still a little confused about the reasoning behind the differfent methods though.

And why we sometimes require/not require the array {}.

 

[Aladin Akyurek]

Thanks,

I'm still a little confused about the reasoning behind the differfent methods though.

And why we sometimes require/not require the array {}.

That's called an array constant. Create a range say in X2:X4 with

X2: Yellow
X3: Blue
X4: Red

Now you can invoke:

=SUMPRODUCT(
    --ISNUMBER(MATCH(A2:A17,X2:X4,0)),
    --(C2:C17< B2:B17),
    --(D2:D17="big"),
    E2:E17)
 
=SUM(
   IF(ISNUMBER(MATCH(A2:A17,X2:X4,0)),
   IF(C2:C17< B2:B17,
   IF(D2:D17="big",
    E2:E17))))

As noted earlier, the latter requires control+shift+enter.