Yes, this is very helpful, thanks very much! Thank you to others on the
Pivot table suggestion as well. You guys are great!!!

"Pete_UK" wrote:

The score column I suggested is the possible answers that people can
give, so it is just the numbers 1 to 4 in successive cells for HR,
then for IT etc. I assumed that this would occupy column B in the
second sheet. Put the formula in C2 and copy across and down, and then
you will end up with a table like this:

Group Score Question1 Question2 Question3 etc
HR 1 12 7 15
HR 2 18 16 11
HR 3 15 5 17
HR 4 10 27 13
IT 1 9 12 7
IT 2 14 8 13
IT 3
IT 4

and so on, giving you the count for each question by Group and by
Score (or Choice - sorry if the word confused you).

Or, you could try pivot tables, as others have suggested.

Hope this helps.

Pete

On Sep 23, 11:11 pm, Tina K wrote:
Hi Pete,

Thank you for the suggestion. But, I'm not quite getting the formula to
work. I do not have a "Score" column (yet) as you suggested. Is that a
calculation or the possible survey answers?
My data looks like this:
A1 B1 C1 D1
Group Q1 Q2 Q3
HR 3 3 3
HR 3 4 2
IT 4 3 4
IT 2 2 3

Sincerely,
Tina



"Tina K" wrote:
Hi Everyone,

I am analzying the results of a survey for our company and I am wondering if
there is a better (smarter) approach in accomplishing what I want.

SCENARIO: I have an Excel spreadsheet full of data from the results of a
survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR,
IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are
random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree,
2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers
from each dept for each question.

My very very long Solution: I sorted by Group (A1), then ran subtotals to
give me the count at each change of group. Next I wrote a Countif formula to
calculate the count for the first group(I added 4 rows underneath the
subtotal count), then wrote another formula next to this to divide this count
to the subtotal count, then formated the # to %. This works fine, but with
the amount of groups and questions ahead, if I did this manually it could
take some time.

Does anyone have another way to approach this?

Thanks in advance,- Hide quoted text -

- Show quoted text -