MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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?

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.

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)

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.