Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Let's say you have a table like this:

 |     A     |     B     |     C     |
-------------------------------------
1|    sex    |    age    | answer q1 |
-------------------------------------
2|     f     |    34     |     3     |
-------------------------------------
3|     f     |    27     |     5     |
-------------------------------------
4|     m     |    29     |     2     |
-------------------------------------
5|     f     |    38     |     4     |
-------------------------------------
6|     m     |    39     |     1     |
-------------------------------------

Now you'd like to split the answers and ages into female and male.

Example for female (this will then be on a second sheet):

 |     A     |     B     |     C     |     D     |     E     |
-------------------------------------------------------------
1|  female   |           |           |           |           |
-------------------------------------------------------------
2|   count   | age-class | age-count | answer q1 | q1 count  |
-------------------------------------------------------------
3|     3     |   20-29   |     1     |     1     |     0     |
-------------------------------------------------------------
4|           |   30-39   |     2     |     2     |     0     |
-------------------------------------------------------------
5|           |   40-49   |     0     |     3     |     1     |
-------------------------------------------------------------
6|           |   Total   |     3     |     4     |     1     |
-------------------------------------------------------------
7|           |           |           |     5     |     1     |
-------------------------------------------------------------
8|           |           |           |   Total   |     3     |
-------------------------------------------------------------

How can I count the occurrences of a specific answer for q1 provided the row being "female"?

For example for A3 I use: =COUNTIF('Ras data'!A:A,"f"). This counts how often the string 'f' occurs in column A of my raw data table.

Is there a specific function to accomplish that goal for the next columns? (i.e. to count the answers of the questions by sex)

Please ignore the age columns. Those are just here to give you an idea of how the table looks. I don't need them later.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

In Excel 2010, you can use COUNTIFS(range1,criteria1,range2,criteria2,...)

=countifs(A2:A200,"f",D2:D200,1)

This will return the count of all females who answered "1" on q1. To return a count for those who answered q1 with "2" it's a minor difference. Both of these will go from row 2 to 200. All criteria must be met for any row to be counted.

=countifs(A2:A200,"f",D2:D200,2)

ALTERNATIVELY...

I've done something similar. But I needed a lot of intermediate data as well. I generated a tally table which conditionally puts a 1 or 0 to count occurrences of certain values. Then it's trivial to sum the columns

For grabbing data from your original table, you might generate a Male tally table and a Female tally table.

----------------------------------------------
| FEMALE |  q1-1 | q1-2 | q1-3 | q1-4 | q1-5 |
----------------------------------------------
| yes    |   0   |   0  |   1  |   0  |   0  |
| yes    |   0   |   0  |   0  |   0  |   1  |
| no     |   0   |   0  |   0  |   0  |   0  |

These rows match your first rows in your first table. To populate the tally table first row, use these functions, going across:

=if(A2='f',yes,no)
=if(A2='f',if($c2=1,1,0),0)
=if(A2='f',if($c2=2,1,0),0)
=if(A2='f',if($c2=3,1,0),0)
=if(A2='f',if($c2=4,1,0),0)
=if(A2='f',if($c2=5,1,0),0)

Then you can drag-fill down however far.

Any male rows will simply have all 0's for their answers, which is what the outside IF does. For female, the inside IF gets called to determine the question's answer. To generate a tally table for male, just duplicate the entire thing, then find/replace the 'f' with 'm'

It's quite a bit of spreadsheet real estate, but it breaks it out per person per answer, so you can analyze quite a bit. You can simplify it by sorting your original data by male and female.

share|improve this answer
    
Great answer, thanks! :) Unfortunately the COUNTIFS function is not available in Google Spreadsheet. I'll try the alternative way then. –  johk95 Jan 5 at 21:42
    
It actually is in the new Sheets. Check this: support.google.com/drive/answer/3256550 and this: support.google.com/drive/answer/3541068 . Though I don't know what's required to switch to "new" sheets. –  Nate Jan 5 at 22:45
    
Or use COUNTIF in an array: productforums.google.com/forum/#!topic/docs/FQfWznmHXho –  Nate Jan 5 at 22:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.