How to add an AND condition in array formula :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 How to add an AND condition in array formula

Terry P
Board Master

Joined: 29 Nov 2002
Posts: 158

Status: Offline

How to add an AND condition in array formula

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

Fri Sep 19, 2003 1:53 pm

Juan Pablo González
MrExcel MVP

Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag:

Status: Offline

Re: How to add an AND condition in array formula

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

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Fri Sep 19, 2003 1:56 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: How to add an AND condition in array formula

quote:
Originally posted by Terry P:
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

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

Fri Sep 19, 2003 1:57 pm

Terry P
Board Master

Joined: 29 Nov 2002
Posts: 158

Status: Offline

Re: How to add an AND condition in array formula

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

Thanks both

Fri Sep 19, 2003 2:04 pm

Juan Pablo González
MrExcel MVP

Joined: 09 Feb 2002
Posts: 8580
Location: Marion, IL
Flag:

Status: Offline

Re: How to add an AND condition in array formula

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))}
_________________
Regards,

Juan Pablo González
MrExcel.com Consulting

Read the Articles List and check out our Recommended links and Add-Ins

Fri Sep 19, 2003 2:06 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: How to add an AND condition in array formula

quote:
Originally posted by Terry P:
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

Thanks both

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.

Fri Sep 19, 2003 2:08 pm

Terry P
Board Master

Joined: 29 Nov 2002
Posts: 158

Status: Offline

Re: How to add an AND condition in array formula

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

Fri Sep 19, 2003 2:11 pm

Terry P
Board Master

Joined: 29 Nov 2002
Posts: 158

Status: Offline

Re: How to add an AND condition in array formula

Sorry Mark couldn't figure it out.
Apologies

Fri Sep 19, 2003 2:43 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: How to add an AND condition in array formula

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?

Fri Sep 19, 2003 2:48 pm

Terry P
Board Master

Joined: 29 Nov 2002
Posts: 158

Status: Offline

Re: How to add an AND condition in array formula

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

Fri Sep 19, 2003 2:58 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: How to add an AND condition in array formula

quote:
Originally posted by Terry P:
...Mark I have used the formula wizard before but only for rudementary formulae.

Thanks both

The Conditional Sum wizard (and, those listed above) are quite different from the Function Wizard. Those listed above "take you by the hand" with step-by-step, contextual instructions. They're quite helpful for novice user.

Fri Sep 19, 2003 3:21 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum