Archive of Mr Excel Message Board

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

double if function; array with two conditions

Posted by Jan on July 11, 2001 8:14 AM

Hello,

I am trying to create a function, which works as follows:

sum (x:x)
if (a="test1")
if (b="test2")

I have no idea how to implement two conditions in an array. Can anybody help me?

Check out our Excel Resources

Re: double if function; array with two conditions

Posted by Mark W. on July 11, 2001 8:32 AM

{=SUM((X1:X10)*(A1:A10="test1")*(B1:B10="test2"))}

Note: This is an array formula which must be entered
using the Control+Shift+Enter key combination. The
braces, {}, are not entered by you, but rather supplied
by Excel in recognition of the nature of the formula.

Re: double if function; array with two conditions

Posted by Steve M on July 11, 2001 9:31 AM

A cleaner way maybe to use the AND function:
if(AND(a="test1",b="test2"),sum(x:x),0)

Re: double if function; array with two conditions

Posted by Mark w. on July 11, 2001 10:22 AM

The multiplication operator, '*', is the equivalent
of the boolean AND() operator. Also, take another
look at your formula... it wouldn't work as constructed.
Give it a try.

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.