Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

array formulas with AND statement

Posted by David on August 02, 2001 1:02 AM
I've asked a similar question before, but I need more help.
I have a conditional statement such as:
=SUM(IF(ABS(B2000-B1000:B3000)<2,1,0)
this will find all the instances where the difference between B2000 and a cell between B1000 and B30000 is less than 2,
and add up the number of times that is true.

This works fine when I enter it as an array formula.
Here is my problem: I want it to satisfy conditions in 2 columns simultaneously, and only only return TRUE when the
conditions are met in both columns. So, I if B2000-B2340 is less than 2, but C2000-C2340 is greater than 2 (or any
number that I set), it is false. But if B2000-B1245 is less than 2 and C2000-C1245 is less than 2, we get a TRUE
result.

I tried =SUM(IF(AND(ABS(B2000-B1000:B3000)<2, ABS(C2000-C1000:C3000)<2),1,0)
but it didn't work, even entered as an array formula (I got a FALSE, ie 0, even when I knew it to be true). Obviously
the problem is with the AND statement. Can I make certain both parts of the AND are entered as an array?

-David


Re: array formulas with AND statement

Posted by Aladin Akyurek on August 02, 2001 3:02 AM
Array-enter (hit CONTROL+SHIFT+ENTER at the same time to enter it)

=SUM((ABS(B2000-B1000:B3000)<2)*(ABS(C2000-C1000-C3000)>2))

or just enter

=SUMPRODUCT((ABS(B2000-B1000:B3000)<2)*(ABS(C2000-C1000-C3000)>2))

Both formulas will give you a count of B and C cells that meet the conditions.

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.