# If Then Else Then Sum

I have this criteria - where Table.FieldA does not equal 'I' then 1 else 0.  Then sum the information from Table.FieldB based on the If/Then statement.  I have a report where I need to put in the information for the opposite information, too - where Table.FieldA does equal 'I' then 1 else 0 and so on.
If {Table.FieldA} <> 'I' Then 1 Else 0 Then Sum ({Table.FieldB}).  When I do this I get a "the ) is missing" message box and it highlights "Then" when this happens.

How do I correctly state this formula?  Thanks.
###### Who is Participating?

Commented:
You are trying to combine 2 actions.  You need to do this in 2 steps.
Try it this way

Create a formula
If {Table.fieldA} <> 'I' then
1
else
0

Save the formula
Drop the formula onto the report
Right click the formula field on the report
Click INSERT --> SUMMARY
Set it for a SUM
Put it in the report footer
You can then delete the formula field from the report

mlmcc
0

Commented:
I'm not entirely sure what you're trying to do, but if you're trying to get the sum of a field, as opposed to a count, then forget the 1 or 0 stuff.

If you want the total of FieldB from the records where FieldA is not 'I', create a formula like the following, put it in the detail section and then insert a summary for that field:

If {Table.FieldA} <> 'I' Then
{Table.FieldB}
Else
0

If you also want a total for FieldB from the records where FieldA _is_ 'I', then create another formula and do a summary on it too:

If {Table.FieldA} <> 'I' Then
0
Else
{Table.FieldB}

James
0

Author Commented:
Although these both solve the issue, it is hard to know exactly how to answer my question without knowing everything about the issues at hand.
I have discovered the following solution:
If {Table.FieldA} <> 'I' and {Table.FieldB} in [0 - 5] Then 1 Else 0
I then went to the Insert>Summary and set it for Sum, and it then tallied correctly.
Thank you.
0

Commented:
OK, so you weren't looking for a total of FieldB, or a straight count.  You wanted a count of the FieldB values between 0 and 5, when FieldA was not "I".

James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.