# Thread: Survey Results Formulas (Excel 97)

1. ## Survey Results Formulas (Excel 97)

I need some help writing a couple of formulas. I am creating a spreadsheet of survey results from a seminar.

1. One question asked users to either check 'satisfactory' or fill in a comment. I want to create a formula which says out of X number of surveys sent out Y number answered 'satisfactory'. I am not sure the exact number or surveys sent out so I want the formula to total the number of surveys sent and tell me out of the total, how many people answered S for satisfactory. The result would look something like 10/50. I am open to other suggestion if you have any.

2. One question asked who have you had contact with A (Assistant), M (Manager) or O (Other). I want a result which totals the number of A, M & Os all in one cell but divided up something like A = 3, M = 8, O = 1.

3. One question asked users to rank 3 considerations when selecting a company. There were 9 options. I want to know what the top 3 overall were.

Any help would be greatly appreciated!!!

2. ## Re: Survey Results Formulas (Excel 97)

Thanks, I am off to a good start.

1. The first part of the formula worked. Is there a way to count the number of cells in a range. Some of these cells are blank. I need to know the total number of cells between D14 and AS14.

2. Some of the cells have two results for example A M. In this case, the formula did not count the A or M. Is there a way to enter the text or write the formula to count these examples?

3. See attached for an example

3. ## Re: Survey Results Formulas (Excel 97)

<img src=/S/blush.gif border=0 alt=blush width=15 height=15>I made a typo, you probably want to use CountA, not count (they are text)
=counta(D14:AS14)
will give a count of the non-empty cells

=Columns(D14:AS14)
will give the count of the cells whether they have anything or not

=count(D14:As14)
will give a count of cells with numbers

2)use wildcards
=COUNTIF(D14:As14,"*A*")
=COUNTIF(D14:As14,"*M*")
etc

3) You could get the 9 counts:

=COUNTIF(\$B\$2:\$B\$100,"*A*")
=COUNTIF(\$B\$2:\$B\$100,"*B*")
...
=COUNTIF(\$B\$2:\$B\$100,"*I*")

Then use large(rng,1), large(rng,2) or large(rng,3) to get the top 3 values, where rng is the range of cells containing the 9 counts.

If you had an attachment with the data filled out I could be more specific...

Steve

4. ## Re: Survey Results Formulas (Excel 97)

<P ID="edit" class=small>(Edited by sdckapr on 25-May-05 13:39. Changed COUNT to COUNTA, as the cells contain text)</P>1) Could be done with a Countif divided by a count.(Change range as appropriate)
=Countif(A1:A100,"S") & " / " & CountA(A1:A100)

2) combine 3 countif
="A = " & Countif(A1:A100,"A") &", M = " &Countif(A1:A100,"M")&", O = " &Countif(A1:A100,"O")

3) I would have to see the setup to understand better.

Perhaps you could post a sample file with some made up results...

Steve

5. ## Re: Survey Results Formulas (Excel 97)

Thanks. I've managed to make the whole thing work. I really appreciate your help. I am getting a much better grasp of formulas! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

6. ## Re: Survey Results Formulas (Excel 97)

I am glad I could help...

Steve

#### Posting Permissions

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