How to add an AND condition in array formula

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Sep 19, 2003

• #2

Just curious, how can the same cell (D4:D998) equal two values ("Restricted" and A6) at the same time ?

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #3

Terry P said:

I have a formula
{=SUM(IF(Payments!$I$4:$I$998=B16,IF(Payments!$D$4:$D$998="Restricted",Payments!$E$4:$E$998,0),0))}

but want to add in an AND statement
Payments!$D$4:$D$998="Restricted" AND Payments!$D$4:$D$998=A6
Brain gone dead!
Please help

Click to expand...

Looks like you're using the Conditional Sum Wizard. You could continue to use it...

 

Upvote 0

T

Terry P

Active Member

Joined

Nov 28, 2002

Messages

256

• Sep 19, 2003

• #4

Sorry Mark just can't get my head around it


Sorry Juan
Payments!$D$4:$D$998="Restricted" AND Payments!$H$4:$H$998=A6
Brain totally dead!

Thanks both

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Sep 19, 2003

• #5

The wizard should give you a version like

{=SUM(IF(Payments!$I$4:$I$998=B16,IF(Payments!$D$4:$D$998="Restricted",IF(Payments!$H$4:$H$998=A6,Payments!$E$4:$E$998,0),0),0))}

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #6

Terry P said:

Sorry Mark just can't get my head around it


Sorry Juan
Payments!$D$4:$D$998="Restricted" AND Payments!$H$4:$H$998=A6
Brain totally dead!

Thanks both

Click to expand...

If the "it" above refers to the Conditional Sum wizard you ought to give it a 2nd look. It takes you by the hand when constructing such formulas.

 

Upvote 0

T

Terry P

Active Member

Joined

Nov 28, 2002

Messages

256

• Sep 19, 2003

• #7

I'll give it a go thanks but may be back! Thanks

 

Upvote 0

T

Terry P

Active Member

Joined

Nov 28, 2002

Messages

256

• Sep 19, 2003

• #8

Sorry Mark couldn't figure it out.
Apologies

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #9

What's there to "figure out". You simply follow the guide provided by the wizard. Have you not worked with a wizard (i.e., Chart Wizard, Text to Columns Wizard, PivotTable Wizard) before?

 

Upvote 0

T

Terry P

Active Member

Joined

Nov 28, 2002

Messages

256

• Sep 19, 2003

• #10

Thanks Juan. I thought I had tried that one but just not on my toes today.

Mark I have used the formula wizard before but only for rudementary formulae.

Thanks both

 

Upvote 0