sum iif not working

[tagteam]

I am trying to sum everything that is not a particular value:



=Sum(IIf([UpdatedResponsibilityCode]<>1 Or [UpdatedResponsibilityCode]<>3 Or [UpdatedResponsibilityCode]<>6 Or [UpdatedResponsibilityCode]<>7,[NetCharge],0))

However, when I do this it actually adds all the values for 1,3,6, 7 instead of excluding them.

[June7]

Because you use OR, try AND.

or

=Sum(IIf(NOT [UpdatedResponsibilityCode] IN (1, 3, 6, 7), [NetCharge], 0))

or

=Sum(IIf([UpdatedResponsibilityCode] IN (1, 3, 6, 7), 0, [NetCharge]))

[ssanfu]

You didn't provide any test data with expected results, so I created my own test data.

I entered 10 records for UpdatedResponsibilityCode and numbered 1 - 10
I entered values 10 - 100 for NetCharge.

Using your formula

=Sum(IIf([UpdatedResponsibilityCode]<>1 Or [UpdatedResponsibilityCode]<>3 Or [UpdatedResponsibilityCode]<>6 Or [UpdatedResponsibilityCode]<>7,[NetCharge],0))

resulted a value of 550..... the sum of all [NetCharge] values

I changed the "OR"s to "AND"s and the result was 380:

=Sum(IIf([UpdatedResponsibilityCode]<>1 and [UpdatedResponsibilityCode]<>3 and [UpdatedResponsibilityCode]<>6 and [UpdatedResponsibilityCode]<>7,[NetCharge],0))

I try not to use negative logic, so I re-wrote the formula. I changed the original formula by changing the not equal to (<>) to equals (=) and moved the [NetCharge] value to the FALSE clause:

=Sum(IIf([UpdatedResponsibilityCode]=1 Or [UpdatedResponsibilityCode]=3 Or [UpdatedResponsibilityCode]=6 Or [UpdatedResponsibilityCode]=7, 0, [NetCharge]))

The result is 380.