Using COUNTIF function in an electronic form

[Coach_Drew]

I have created a form that I plan to email to clients to complete. I am instructing them to read the question and if it is true for them, to put an X in the answer field. I am trying to use a COUNTIF formula to count the number of X's given for certain questions. Then at the bottom of the form, there is a self-scoring box that summarizes their responses.

For example:
A B C D E
I like the color red
2 x I like the color orange
3 I like the color yellow
4 I like the color green
5 The color red makes me happy
6 x The color orange makes me happy
7 The color yellow makes me happy
8 The color green makes me happy
9
10 Red Orange Yellow Green
11 0 2 0 0

My formula is going into the fields for B11, C11, D11 and E11. I used this formula in cell B11:
=COUNTIF(A1:A5,"=*x*")
And this formula in cell C11:
=COUNTIF(A2:A6,"=*x*")
And so on...

Except that this formula is not working in my Excel form. When I tested out my actual form, it returned:
Red Orange Yellow Green
29 30 31 33

When I went back and manually counted the number of occurrences, it should have returned:
Red Orange Yellow Green
1 8 10 10

Any ideas what I am doing wrong and how I can fix it? I did change my cell formatting to General (then Number, then Text, but nothing changes)


Thanking you in advance!
Drew

 

[Marcelo Branco]

Welcome to Mr Excel

Try this

B11
=COUNTIF($A$1:$A$10,"*x*"&B10&"*")
copy (drag) across till E11

Hope this helps

M.

 

[Marcelo Branco]

I edited my first formula

Try

B11
=COUNTIF($A$1:$A$10,"*x*"&B$10&"*")
copy (drag) across till E11

M.