Sum with criteria

[icsupt via AccessMonster.com]

I have 41 fields that have 6 different possible answers. The fields are
named:
1a
2a
3a
4a
....etc

The 6 possible answers are: Excellent, Very Good, Good, Fair, Poor, Terrible.


I want to total up how many times in questions 1a-41a each possible answer
occurred.

Here's my formula, but it's not summing up correctly.

=Sum(IIf([1a]="Excellent" Or [2a]="Excellent" Or [3a]="Excellent" Or [4a]
="Excellent" Or [5a]="Excellent",1,0))

What am I missing?

Thanks in advance.

 

[Duane Hookom]

How about showing us a few real examples and how you would like them to be
totaled?

Your table structure isn't normalized which causes issues like you are
experiencing. A demo survey "At Your Survey" is available for download and
review at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane