Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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

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


Check out our Excel Resources

Re: Why doesn't this work,=SUMIF(A:A,"AND(>100,<200)",B:B)

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<200,B:B)))

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

Aron


Re: Why doesn't this work,=SUMIF(A:A,"AND(>100,<200)",B:B)

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 <100) expresses 2 conditions. That's why SUMIF with AND(A:A > 100, A:A <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 <100)}

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

Aladin

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.