Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
blocks of data repeating | New Users to Excel | |||
repeating columnar data | Excel Worksheet Functions | |||
repeating data in other sheets | Excel Discussion (Misc queries) | |||
Repeating Data | Excel Discussion (Misc queries) | |||
How do I stop data from repeating? | Excel Discussion (Misc queries) |