Subtotals of repeating set of data
 

I am working on an analysis of responses to a survey. There are 50 questions
which are broken up into 6 sections. I need to provide a subtotal for each
section. The responses are levels of agreement - Strongly Agree, Agree,
Disagree, Strongly Disagree or NA. See the layout below:
Column A Col.B Col.C
Question 1
Line 5 SA 2
Line 6 A 3
Line 7 D 7
Line 8 SD 2
Line 9 NA 1


Question 2
Line 13 SA 3
Line 14 A 1
Line 15 D 2
Line 16 SD 8
Line 17 NA 0


After Question 6 - I need subtotals for the first section - Questions 1-6 -
for each level of response: SA,A,D,SD,NA. Is there a way to do a sum for this
layout....other than to have to specify each line number in the formula?

Any help will be appreciated!

Blue Bunny

Subtotals of repeating set of data
 

Assuming your data follows the same pattern (i.e. 2 blank rows between
each set of question/answers), then in cells B53:B57 you will need to
put SA, A, D, SD and NA, and then in C53 you can put this formula:

=SUMIF(B$4:B$50,B53,C$4:C$50)

This can be copied into C54:C57 to give you the subtotals for each of
the choices. For the other sections, you will need to adjust the
ranges to suit.

Hope this helps.

Pete

On Oct 8, 9:01 pm, Blue Bunny
wrote:
I am working on an analysis of responses to a survey. There are 50 questions
which are broken up into 6 sections. I need to provide a subtotal for each
section. The responses are levels of agreement - Strongly Agree, Agree,
Disagree, Strongly Disagree or NA. See the layout below:
Column A Col.B Col.C
Question 1
Line 5 SA 2
Line 6 A 3
Line 7 D 7
Line 8 SD 2
Line 9 NA 1

Question 2
Line 13 SA 3
Line 14 A 1
Line 15 D 2
Line 16 SD 8
Line 17 NA 0

After Question 6 - I need subtotals for the first section - Questions 1-6 -
for each level of response: SA,A,D,SD,NA. Is there a way to do a sum for this
layout....other than to have to specify each line number in the formula?

Any help will be appreciated!

Blue Bunny

Subtotals of repeating set of data
 

Pete-this worked great! Solved this problem, plus I was able to use this
concept to apply to another SUMIF situation. I'm moderately new to using the
fx formulas.... other than the basics.
Thanks for your help!
Blue Bunny

"Pete_UK" wrote:

Assuming your data follows the same pattern (i.e. 2 blank rows between
each set of question/answers), then in cells B53:B57 you will need to
put SA, A, D, SD and NA, and then in C53 you can put this formula:

=SUMIF(B$4:B$50,B53,C$4:C$50)

This can be copied into C54:C57 to give you the subtotals for each of
the choices. For the other sections, you will need to adjust the
ranges to suit.

Hope this helps.

Pete

On Oct 8, 9:01 pm, Blue Bunny
wrote:
I am working on an analysis of responses to a survey. There are 50 questions
which are broken up into 6 sections. I need to provide a subtotal for each
section. The responses are levels of agreement - Strongly Agree, Agree,
Disagree, Strongly Disagree or NA. See the layout below:
Column A Col.B Col.C
Question 1
Line 5 SA 2
Line 6 A 3
Line 7 D 7
Line 8 SD 2
Line 9 NA 1

Question 2
Line 13 SA 3
Line 14 A 1
Line 15 D 2
Line 16 SD 8
Line 17 NA 0

After Question 6 - I need subtotals for the first section - Questions 1-6 -
for each level of response: SA,A,D,SD,NA. Is there a way to do a sum for this
layout....other than to have to specify each line number in the formula?

Any help will be appreciated!

Blue Bunny