1. Registered User
Join Date
Jul 2003
Location
England
Posts
26

hello everyone

I am doing some array formulas to summarise a list of data.

I want to sum withdrawals and closures together, can anyone let me know how to go about it.

This is what I have got so far

=SUM(IF(\$C\$5:\$C\$35="S",IF(\$D\$5:\$D\$35="W",IF(\$E\$5:\$ E\$35<>"IT",IF(\$I\$5:\$I\$35=1,\$F\$5:\$F\$35,0),0),0)))

When I add another criteria for closures then it just excludes all the others, cloumn D cant be W - withdrawal and C - closure, it would just be one or the other.

thanks

2. Registered User
Join Date
Oct 2003
Posts
1,091
If I understand what you are trying to do, then I think your problem is nesting the IF statement within the SUM function. Try it this way (keep in mind that you do not enter the { } brackets - those are entered by Excel when you press CTRL + Shift + Enter):

{=IF((\$C\$5:\$C\$35="S")+(\$D\$5:\$D\$35="W")+(\$E\$5:\$E\$35 ="IT")+(\$I\$5:\$I\$35=1),1,0)*(\$F\$5:\$F\$35)}

The + sign functions as "OR" so multiple conditions are met.

3. Registered User
Join Date
Jul 2003
Location
England
Posts
26
How do I do it so that only column D is "or" criteria, all of the others are "and"

This is my criteria

Cloumn C is S
Column D is W or C
Column E is IT
Column I is 1
Calcluate the total of column F

Thanks,
Beth

4. Registered User
Join Date
Oct 2003
Posts
1,091
For AND operation use * instead of +

5. Registered User
Join Date
Oct 2003
Posts
1,091
Going back to your original formula, don't make it nested - it won't work: something like this

=SUM(IF((\$C\$5:\$C\$35="S")*((\$D\$5:\$D\$35="W")+(\$D\$5:\$ D\$35="C"))*(\$E\$5:\$E\$35<>"IT")*(\$I\$5:\$I\$35=1),\$F\$5: \$F\$35,0))

I tried this and it works well. Be sure that you use CTRL + SHIFT + ENTER, and not ENTER.
Last edited by shades; 11-12-03 at 11:13.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•