Why doesn't this work,=SUMIF(A:A,"AND(>100,

Posted by Trevor on January 21, 2002 5:41 PM

Hi.,

I know I have previously asked advice on a similar matter, but I don't understand why this doesn't work.
If I have only one condition it is ok. =SUMIF(A:A,">200",B:B)
But when I introduce the AND function it doesn't work
I have tried CSE .

sorry for labouring this

Col A = numbered part number 100 to say 2500
Col B = price of Part
Answer required cost of all parts in the 101 - 199, 201 - 299 etc (100,200,300 etc are catagory types with no cost which will hold the Sumif formula)
I know there are other solutions ( two sumifs), but I am curious why the AND function does not work here.

tia

Posted by Aron on January 21, 2002 9:33 PM

I'm not sure why the AND() function doesn't work with this. I had the same problem recently. Use the SUM() with nested IF()s and it will work. That is:

=SUM(IF(A:A>100,IF(A:A&LT;200,B:B)))

Then hit CSE. This should work for you...

Aron

Posted by Aladin Akyurek on January 21, 2002 9:52 PM

> I know I have previously asked advice on a similar matter, but I don't understand why this doesn't work.
> If I have only one condition it is ok. =SUMIF(A:A,">200",B:B)
> But when I introduce the AND function it doesn't work

SUMIF like COUNTIF by design calculates a value based on a condition.
AND(A:A > 100, A:A &LT;100) expresses 2 conditions. That's why SUMIF with AND(A:A > 100, A:A &LT;100) as its condition/criteria argument will not work.

> I have tried CSE .

Has nothing to do with it. Besides,

{=AND(A:A > 100, A:A &LT;100)}

will not work because an array formula, again by design, does not accept whole columns as range arguments.

Aladin

=============