Data summary using Index or countif

[Demona]

I have a survey questionairre which asked for one or multiple answers.
Now I need to summarize the date and facing the belwo issue:

The question was asked to select one or more of the below options:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Options[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]AMER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64"]APAC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR]
[TD="class: xl66, width: 502"]Legislation[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR]
[TD="class: xl66, width: 502"]Agency[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 502"]
<tbody>[TR]
[TD="class: xl66, width: 502"]Others[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The final data received from the survey is in the form of below:
[TABLE="width: 630"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]AMER[/TD]
[TD]APAC[/TD]
[/TR]
[TR]
[TD]Legislation[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Agency[/TD]
[TD] 2[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Others[/TD]
[TD] 3[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Legislation, Agency[/TD]
[TD] 4[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]Agency,Others[/TD]
[TD] 1[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]Legislation, Agency, Others[/TD]
[TD] 1[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] 12[/TD]
[TD] 11

[/TD]
[/TR]
</tbody>[/TABLE]

I need to summarise the total count on each of the 3 options for each region. I tried using countif, it did not work. I think I need to use Index formula s this is not simple vlookup case. Your assistance is appreciated.

 

[Mackers]

Hi

Try this:

=SUMIFS(AMERColumn, RowLabelsColumn, "*"&A2&"*")

Where A2 contains "Legislation", AMERColumn is the 1,2,3,4,1,1,12 column from your final data, and RowLabelsColumn contains Legislation, Agency, Others etc. from your final data. This works by summing the number based on the first column containing for example "Legislation" somewhere in the string, as I have appended wildcards * either side of the term.

Hope that helps

Mackers