| View previous topic :: View next topic |
| Author |
Message |
NiceGuy88
General User
Joined: 05 May 2012
Posts: 7
|
 Posted: Tue May 08, 2012 1:55 am Post subject: Survey Responses??? |
 |
|
I've made a survey and the results are automatically added to a spreadsheet which I import(copy/paste) to open office to do all my formulas on.
One question is a Check box question with multiple answers. It posts these into a single cell split up with a comma, for example:
option1, option2, option3
FOR THE LIFE OF ME I cant get it to add up the individual 'options' and list them in another cell:
option1 =
option2 =
option3 =
I keep trying COUNTIF formulas but only seems to work if there is only one option in the cell!
Can someone please give me a formula, or a macro to do this....I would be forever in your debt!
Thanks |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 08, 2012 2:33 am Post subject: |
|
|
Are you running out of cells? There are a billion cells on every sheet.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
NiceGuy88
General User
Joined: 05 May 2012
Posts: 7
|
| Posted: Tue May 08, 2012 2:47 am Post subject: re:Survey Responses??? |
|
|
That's not the issue. The data is recorded into the cell automatically from the survey so I don't want to change how it is otherwise it wont match up with the survey. I just want to find out how many people picked option 2 for example.
A1 - option1, option2, option3
A2 - option2
A3 - option1, option3
WITHOUT changing whats in column A.....how can I add them up?
Thanks[/list] |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Tue May 08, 2012 5:18 am Post subject: |
|
|
Either you design your input properly or you need some sophisticated formulas. Rubbish in, rubbish out.
| Quote: | | I keep trying COUNTIF formulas but only seems to work if there is only one option in the cell! |
Use a regular expression or turn off the "match whole cell option"
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1848
Location: Sydney, Australia
|
| Posted: Tue May 08, 2012 5:35 am Post subject: |
|
|
When this option does not have a tick in the checkbox...
Search criteria = and <> must apply to whole cells
The COUNTIF function counts all cells that contain the option being counted regardless of other text in the cells.
If A1:A3 has...
option1, option2, option3
option2
option1, option3
=COUNTIF(A1:A3;"option1") returns 2.
When the above option has a tick in the checkbox the same formula would return 0 because none of the three cells has "option1" only.
To get to the option go Tools|Options...|OpenOffice.org Calc|Calculate
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
|
