Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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

> 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

> 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.

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.