Filter / COUNTIF with multiple values in one cell resulting from Google survey

Hi Excel Help Forum,

This is a cross-post related to http://www.excelforum.com/excel-gene...-formulas.html.

I am working with survey results from a Google survey. Some of the questions were checkbox questions, which means they allowed to tick multiple answers. As a result, some cells in my spreadsheet contain multiple values. Some only have one, others two, others more than that.

What I am trying to do is to filter the cells (according to state of the respondent, for instance) and then count the values in the cell. Filter and COUNTIF would be the perfect option. I have tried to use this SUBTOTAL/OFFSET formula to get around it

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Overview!Z2:Z27,ROW(Overview!Z2:Z27)-ROW(Overview!Z2),0,1))*(Overview!Z2:Overview!Z27="Y")).

The problem I am facing is that this formula only counts the cells that contain one value ("Y"). It does not count the "Y"s in cell where there are multiple values. Is there any way around this?

Many many thanks for your help.

Shikriti

Hi Shikriti

Are you sure that OFFSET part is correct.

Hi there,

Thanks for the quick replies.

KBkumar - I am attaching an extract of the database. For instance, I would need to know who the main funders (F) are for national civil society (B) in Alambama (A). Do let me know if this is helpful or if you need a broader extract.

Kevin - well, not really sure if the offset part is correct. But it does produce a correct count of cells where the value was "Y" only.

Thanks ever so much for trying to help,

Shikriti

YOU ARE THE BEST!! It worked! Thank you soso much, you have no idea how much you are helping me here. You made my day. Thank you!!

Since you guys are so good at this:

The reason why I am trying to work as outlined above is that I have not been able to use pivot tables for cells which contain multiple values. For example, if I wanted to count the main funders (Column D in the sample attached) per state (column A), I get a long list with all the different combinations of answers. What I would need however is a pivot table with a count of the individual values (for instance "individual donations" which appear in the combinations. Can anybody help?

Many thanks everybody