How can I do this in spreadsheets?

Question

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.

Answer 1

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.