Formual help needed

[Jan]

I'm using Excel 2003.

How would I write a formula to accomplish the following:

If column A = "Me" and
column B does not equal "grapes", "oranges", "apples", "green bean" or
"corn" then
Sum column D.

[dominicb]

Good evening Jan

Hope you're ready for this one! First of all, in testing the formula I only used the range from row 6 to 12 using column B for the value of "me" and using column C for the assorted fruit and veg and D for the values, so you will have to alter your ranges accordingly. Secondly, this is an array formula, which means you do not press enter to commit the formula, but ctrl + shift + enter. This will put the formula into a set of curly braces which are not shown here.

=SUM(IF($B$6:$B$12="me",IF($C$6:$C$12<>"apples",IF($C$6:$C$12<>"corn",IF($C$6:$C$12<>"grapes",IF($C$6:$C$12<>"green bean",IF($C$6:$C$12<>"oranges",$D$6:$D$12,0),0),0),0),0),0))

HTH

DominicB