Excel - Counting cells that contain same numbers

[tomatodogo]

Hi There,

I am working on compiling data from a survey my company recently conducted. We have about 200 surveys with 14 questions on each. For each question, the participant gives a number of 1-5. Basically, I am trying to figure out what formula I need to use in excel for it to automatically calculate how many 1's, 2's, 3's, 4's, and 5's are listed in each row. I have figured out how to count how many cells contain a number, but cannot seem to get it to count how many of one specific number. Does anyone have any idea how to do this?

Help is much appreciated.

Sara

[Paul]

Hi Sara, and welcome to the forum.

We don't have your sheet layout, but you can probably use the COUNTIF function, for example:

=COUNTIF($A$1:$A$20,1)

That will count all of the 1's in A1:A20. Change the last 1 to 2, 3, 4 and 5 for the others.

[tomatodogo]

Thank you Paul! What a quick response. I see what you mean about that formula. It works when I use it on the columns. The only problem is, the data is compiled in rows. For instance, I need to make it work for B2-G2. When I put the letters in the formula in place of the numbers, Excel says there is an error in my formula. Should I change it so that the data that needs to be added is in columns instead of rows? Or can I change the formula. Also, when I copy the formula to a new column, instead of automatically adapting itself to reflect the new column, it keeps the old column's letter in the formula. Do you know how I can fix that?

I hope this makes sense.

Thank you again so much,
Sara

[tomatodogo]

Paul,

I just tried it again, and it is working in the rows now. The only thing I am wondering now, is how to copy and paste the formula so that it automatically adapts to a new row.

Thank you!!!!!!!!!!!!!!!!!
Sara

[Paul]

Hi Sara,

To make it automatically change the columns and/or rows, remove the $ signs from the formula. $'s make the cell references static.

There's four ways to reference a cell:
$A$1 ..will always point to cell A1
$A1 ..will always point to column A, but the row number will change when copied up/down
A$1 ..will always point to row 1, but the column letter will change when copied left/right
A1 ..column and row values will change when copied left/right/up/down

[tomatodogo]

PAUL! YOU ARE MY HERO!!!!!!!!!!!!!!!!!!!!

You have no idea how long I have been trying to figure this out. You just made my life so much easier.

THANK YOU THANK YOU THANK YOU!!!!!!!!!!!!!!!!

[Paul]

You're welcome!!!

Glad I could help make someone's life easier. It was bound to happen at some point!